Power Query: Fascinating Filters

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():

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:

I can edit this to use List.Contains():

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!