Filter Data with Multiple Criteria using Power Query

Recently while on a consulting assignment, I had the need to dynamically extract data based on filters given by the user. Although you have the ability to apply filters (just like Excel) in Power Query but in this post I am going to share that how can you apply dynamic filters (filter criteria that can change) using Power Query

And to make this a little more spicy, I’ll delve into a 3 scenarios to explain the problem in different contexts

 

#1 – Filter Data with a Single Filter Criteria

Let’s say you would like to filter the Region Column in your data by the value the user inputs in a cell

Assuming that you know how to load the Data (both tables) in Power Query, let’s see what comes next.

1. I am going to right click on the value “West” in the Filter Query and Drill Down.
2. Drilling down enables me to return a query with a single value (even if the user specified 2 values)
3. I then apply a pseudo filter “East” and then replace that with the “Filter” query in the formula bar
4. Finally close and load
5. Now each time the user changes the Region, I’ll have the updated data. Bingo!

 

#2 – Filter Data with Multiple Filter Conditions from a Single Column

Someone would definitely argue the flexibility to filter Region by multiple values. Can we? Sure we can, but in this case I would slightly change the approach and before I explain it, I’ll share why the earlier approach won’t work!

Remember drilling down on the first row? And because we did that the query will always have a single value for the first row, even if the user inputs 2 values. So now you know that won’t work.

Let’s load both the tables again (with Region filters having 2 values instead of 1) and let’s try this instead..

1. In your Data Query go the Home Tab >> Merge Queries
2. Select the 2nd query as Filter (with 2 rows i.e. multiple filters on Region Column)
3. At the bottom the Join Kind will be Inner (which means only matching rows will be picked up)
4. Done! And yeah…the extra column that gets created, please remove that

 

#3 – Filter Data with Multiple Filter Conditions from a Multiple Columns

It would not be so uncommon to come across the need to filter data with multiple columns with multiple filter conditions. Well if you did understand the previous one, this is just going to be a cakewalk

Everything remains the same except one thing

1. Since you’ll now have 2 columns as your filter criteria (region and sales rep)
2. You’ll need to hold the Ctrl Key and select both of them and
3. Subsequently select both of them in the Data
4. Note that the order of selection is important (watch for the numbers 1 & 2 against the column headers)
5. Remove the extra column, that’s it. Done!

 

#4 – Filter Data with Multiple Criteria using Power BI – Creating Parameters

All of the above works like a charm in Excel, since you have the convenience of writing values in cells. But in Power BI you’ll have to create a Parameter to be able to dynamically filter the data.

Difference being – The user, unlike excel will enter the Filter condition in the parameter

Creating a Parameter can’t get easier than this

1. Power Query window >> Go to Home Tab >> Manage Parameter >> New Parameter
2. Specify the value in the Pop up
3. Done

All that you need to do once you have created a parameter is replace the hard coded value in the formula bar with the name of the Parameter. Exactly same as we did it in Excel!

 

#5 – Filter Data with Multiple Filter Conditions from a Multiple Columns in Power BI

This too remains exactly like Excel, the only change being that

1. You’ll have to source your filter conditions table from Excel
2. You can also type the filter conditions and create a quick table using Home Tab >> Enter Data Option
3. And once you have the table then the usual Merge using Inner Join works beautifully

 

More of a Video Person ?

More Power Query Tricks

1. Separate Numbers from Text – Using Excel and Power Query
2. SUMIF in Power Query
3. How to do a Vlookup in Power Query
4. Data Clean up Challenge – Power Query
5. Unpivot Data with 2 Headers