Power Query Data Load Optimization

To improve the performance of your Power Query data load, you can optimize your filtering process by using M functions and techniques. Here's a more efficient approach:

1. Load all three data sources into separate queries in Power Query.
2. Create a new query that filters Source A by the desired names using the List.Contains function. Use a parameter to store the desired names, so you can easily change them later.
3. Merge Source A and Source B based on the "Name ID" column to get the associated principals for each media. Use the List.Contains function here as well.
4. Merge the resulting query with Source C based on the "Film ID" column to get the basic information of the filtered media.
5. Close and load the final query into Power BI.

By merging the queries instead of filtering them multiple times, you reduce the number of operations and improve performance. Here's an example of the M code for each step:

Step 2:

let
    SourceA = SourceAQuery,
    DesiredNames = {"Name1", "Name2", "Name3"}, // Replace with your desired names or use a parameter
    FilteredSourceA = Table.SelectRows(SourceA, each List.Contains(DesiredNames, [Name])),
    SourceB = SourceBQuery
in
    Table.Join(FilteredSourceA, "Name ID", SourceB, "Name ID", JoinKind.Inner)

Step 4:

let
    MergedAandB = MergedAandBQuery,
    SourceC = SourceCQuery
in
    Table.Join(MergedAandB, "Film ID", SourceC, "Film ID", JoinKind.Inner)

Follow these steps to optimize your data load in Power Query and improve performance. Additionally, consider using other performance optimization techniques like reducing the number of columns loaded, transforming data at the source, and using the Power Query Editor's filtering capabilities effectively.

If you want to learn more about Power Query and its advanced functionalities, I recommend checking out the courses offered by Enterprise DNA at https://enterprisedna.co/.