Power Query: Fascinating Filters
15 May 2019
Welcome to our Power Query blog. Today, I am going to look at how Power Query turns a tricky transformation into a simple task.
I am worried about the expenses my imaginary salespeople are incurring. I have identified target areas that I need to analyse. I have a query with my target areas, and I have a query with my salespeople’s data – I want to filter their data using my target area query.
I create two queries ‘From Table’ in the ‘Get & Transform’ section of the ‘Data’ tab.
I am in the ‘Salespeople_Expenses’ query, and I can see the ‘Target_Expenses’ query in the left-hand pane. I want to filter Expense by the column in query ‘Target_Expenses’ (I have called the column Target Expenses). The method I am going to use is to treat Expense like a list. I used similar functionality in Power Query: Words Are Key, but this time, instead of List.ContainsAny(), I am going to use List.Contains():
List.Contains(list as list, value as any, optional equationcriteria as any) as logical
This returns true if a value is found in a list. I also have the option of specifying a condition (equationcriteria), but I won’t need that optional parameter in this example.
In order to get the M code needed to apply a filter, I first apply a standard filter to Expense.
I use the downward arrow icon next to the title of Expense, and I opt to filter on the value ‘Petrol’.
This gives me the generated M code:
= Table.SelectRows(#"Renamed Columns", each [Expense] = "Petrol")
I can edit this to use List.Contains():
= Table.SelectRows(#"Renamed Columns", each List.Contains(Target_Expenses[Target Expenses], [Expense]))
When I click on the tick (or press RETURN), I expect to see only those expenses that I am targeting.
I can now focus on the expenses that concern me, so I choose to ‘Close & Load’ my query to a worksheet in my Excel workbook. If other expense types are called into question, they can be added to my original worksheet.
I refresh my query to see the results.
The latest expense code to cause concern has been added. I have renamed my sheets to make the functionality clearer.
Come back next time for more ways to use Power Query!