Query Filtering Through Other Queries/Lists

I am trying to explore/learn ways to better query data through Power Query for use in Power BI.

I am using 3 data sources
Source A. Basic information of Principals (Cast & Crew of Media) - Contains Name ID
Source B. Media with their associated Principals - Contains Name ID & Film ID
Source C. Basic information of Media - Contains Film ID

My proposed flow within Power Query is the following,

  1. Manually filter source A by the desired names
  2. Reference source A and convert to a List of Name IDs
  3. Filter source B by List of Name IDs
  4. Reference source B and convert to a list of Film IDs
  5. Filter source C by list of Film IDs

My thought is that once this is set up, I can change the names in step 1 and the rest would flow with a data refresh.

The filtering I am doing through a List.Contains & List.Buffer
i.e. “= Table.SelectRows(#“Renamed Columns”, each List.Contains(List.Buffer(List_NameIDs),[Name ID]))”

It works, but the dataset takes an extremely long time to load. Is there a better way to achieve this?

Have you tried Data Mentor on this.

Have a look for some ideas on this specific question.

The key is to breakout each step and see where the hang up really is.

You could even break down each step and get advice on each of these so work towards a better overall solution

@SamMcKay This worked perfectly! It was also a great introduction to the new Data Mentor. Kudos to you and the team for launching a fantastic tool.

Thank you sir!

1 Like