Query Filtering Through Other Queries/Lists

Power Query
1

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?

2

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.

3

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

4

@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