Dynamic Filter by a Range of Values in Power Query

Applying filters to a column in Power Query is a pretty common. Often these filters could be for a single value or multiple values but these are mostly constant filters i.e. to change these filters you’ll have to edit the filtered step.

In this blog, I’ll discuss how to apply a dynamic range of values as a filter on your dataset using power query, which means the user can change these values in Excel and the output will update automatically.

 

Start with a Video – Filter by a Range in Power Query

 

Consider this data

We have two data points,

• A two-columnar data having category and value
• A table with a range of values to be applied as a filter to the Category column of the Data. The user will be able to change these values as desired.

 

Let’s start with loading these 2 tables in Power Query.

# Step 1: Convert the filter table into a list –  Since a list makes it easier to apply filters, I’ll convert this table to a list by right-clicking on the Header >> Drill Down.

 

# Step 2: Apply a filter manually on the category column of the Data and tweak the M code to make it Dynamic.

Once I apply any random filter on the category column of the data, below M Code gets generated.

Here category “B”  and “C” are hardcoded, in order to make it dynamic, I have modified (see highlighted) the M code such that the filter is applied based on the list that we created from the Filtered Table..

= Table.SelectRows(Source, each List.Contains(Filter,[Category]) = true)

And that is it! Close and load the Query.

Now the user can update values in the filter table and refresh the query to dynamically apply the filter on the data.

 

 

Dynamic Filter by Range of Values – Scenario 2

You may also want to do an action based on the matching values of the filter list.

1. In this scenario, I am going to add a new column.
2. In the new column, I have used the same List.Contains formula and wrapped with a simple IF to classify category as Best Products.

if List.Contains(Filter, [Category]) 
then 'Best Product' 
else 'Avg Product'

Here is the output

Will highly recommend checking out this post on List.Contains, lot of gold nuggets in there.

 

 

A few more Power Query / Excel Tricks

1. Filter Data with Multiple Criteria using Power Query
2. 3 Ways to Filter Data in Excel
3. Filter Data by Selection (Quick Tip)
4. Create Dynamic Data Types in Power Query
5. Nested Let Statement in Power Query
6. Expand All Column Dynamically in Power Query