Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

How to filter by a list in Power Query (4 methods)

Power Query - filter by a list

Filtering is a common task in Power Query. Usually, we filter by a single value, or maybe a small number of known values. These filter values are hardcoded into the underlying M code. Therefore, to change the filter, we have to edit the query. But what if we don’t know which items we want to filter by, or how many items there are? Well, that’s what we’ll find out in this post, by looking at how to filter by a list in Power Query.

Download the example file: Click the link below to download the example file used for this post:

Watch the video

Watch the video on YouTube


The example data we are working with in this post is as follows:

Example data for filtering a list in Power Query

The examples all use data in Excel Tables, but could be from any sources. The Table names are:

  • Data: The data we wish to filter
  • FilterList: The values we want to filter the Item column of the Data table by
  • ColumnFilter: Used in the example below to demonstrate how to filter the columns in the output.

In the example file, all three tables are loaded into Power Query.

Understand filtering in Power Query

Let’s start by filtering with the user interface to understand the M code syntax.

Click on the filter button at the top of the Item and select Alpha and Charlie.

Filter a table with hardcoded values

The M code for this step is:

= Table.SelectRows(#"Changed Type", each ([Item] = "Alpha" or [Item] = "Charlie"))

Let’s break this formula down

  • Table.SelectRow: The formula for selecting rows in a Table
  • #”Changed Type”: The name of the table to filter (usually the name of the previous step)
  • each: A keyword to indicate the following code section is applied on a row-by-row basis
  • ([Item] = “Alpha” or [Item] = “Charlie”): A logical test to check if the value in the Item column equals Alpha or Charlie

The logical test is the element we need to change for more advanced filtering.

If we amended the formula as follows, all the rows are returned.

= Table.SelectRows(#"Changed Type", each true)

Or, if the formula were as follows, none of the rows are returned:

= Table.SelectRows(#"Changed Type", each false)

Therefore, to filter based on a list, we need a formula that returns True or False for each row in the table. True items are retained, false items are excluded.

Filter by a list

To filter by a list, we use the List.Contains function.

List.Contains syntax & arguments

List.Contains checks if a value exists inside a list. The syntax and arguments for List.Contains are as follows:


List.Contains(list as list, value as any, optional equationCriteria as any) as logical


  • list: the list of values
  • value: the value to check if it exists in the list
  • equationCriteria: determines how values are compared (e.g., whether to ignore case)

The equationCriteria argument can become quite complex. This is outside the scope of this post. To find out more, check this out:

Result: The output of List.Contains is a logical value (i.e., True or False)

More info: Find more information on List.Contains here:

Convert the FilterList table into a list

The FilterList query is currently a Table; we need to convert this into a list.

Select the FilterList query, then click Transform > Convert to List

Transform - Convert to List

Amend the Table.SelectRows function

Select the Data query, and find the Filtered Rows step we created earlier. Amend the formula as follows:

= Table.SelectRows(#"Changed Type", each List.Contains(FilterList, [Item]))
  • FilterList: The name of the list query
  • [Item]: The name of the column to filter. The each keyword ensures this comparison occurs row by row

The preview window displays the result:

Filter by a list using List.Contains

Currently, case is considered in the filter; “alpha” and “Alpha” are considered different values. To ignore the case, we would add an equationCriteria argument to the formula as follows:

= Table.SelectRows(#"Changed Type", each List.Contains(FilterList, [Item], Comparer.OrdinalIgnoreCase))

Testing the solution

Close and load the queries and return to Excel.

Change the values in the FilterList table, then click Data > Refresh All.

Ta Dah! The query refreshes to show only the items in the list.

Do we have to convert FilterList to a list?

We may have other uses for the FilterList query; therefore, we may not want to convert it to a list.

In that scenario, we could create the list inside the List.Contains function; this avoids changing the FilterList table query.

= Table.SelectRows(#"Changed Type", each List.Contains(FilterList[Filter List], [Item]))
  • FilterList: the name of the query
  • [Filter List]: the name of the column to convert to a list

Filter to exclude items

Another common scenario is to filter out the items from the table. To achieve this, we add the word not to the function; this flips the True and False results around.

 Table.SelectRows(#"Changed Type", each not List.Contains(FilterList, [Item]))

Merge – alternative faster approach

If we have a reasonably simple filter scenario, but a lot of data, we can use the merge transformation as an alternative.

In the merge transformation, assuming we have the Data table first, and the FilterList table second, we use a Right Outer join to return only the items in the filter table. See the example below:

Merge transformation for faster filtering

Once the merge is returned, we can delete the merged column as it is not required. This creates the same effect as filtering by a list.

Merge vs List.Contains – which is faster?

I tested the Merge and List.Contains methods for speed; the result is surprising.

Test #1: 100,000 rows of 8 unique values, which is filtered by 2 values:

  • List.Contains: 18.2 seconds
  • Merge with right-outer join: 1.6 seconds

Test #2: 100,000 rows of 8 unique values, which is filtered by 4 values:

  • List.Contains: 26.3 seconds
  • Merge with right-outer join: 1.7 seconds

This shows that merge is significantly faster.

Filter columns by a list

OK, we now understand how all this works. Time to take it to the next level. Let’s filter the column names to return only the columns we want.

Convert the ColumnList query to a list by selecting it, then clicking Transform > Convert to List.

In the Data query, select some columns, then click Home > Remove Columns (drop down) > Remove Other Columns.

The M code for the step looks like this:

= Table.SelectColumns(#"Filtered Rows",{"Date", "Item", "Model"})

The M code lists the columns we retained. To make this dynamic, we just need to replace the list of hard-coded values with our ColumnList query.

= Table.SelectColumns(#"Filtered Rows",ColumnList)

This also places the columns in the same order as the list.

Note: I recommend performing this step at the end, because if other steps reference columns that have been removed, it will cause an error.

Dynamic filtering

OK, now for our final example. Let’s suggest we want to return only the rows for the top 3 Models. However, over time, as our data changes, so will the top 3 models. Our list to filter by is not fixed. Therefore, we need to make this completely dynamic.

Select the Model column, then click Transform > Group By.

In the Group By dialog box, enter the following:

  • New Column Name: Total
  • Operation: Sum
  • Column: Value
Group By dialog box settings

Click OK to close the Group By dialog box and display the summarized values.

To sort the values, select the Total column, then click Home > Z-A.

Next, we need to keep only the top 3 rows. Click Home > Keep Rows (drop down) > Keep Top Rows

In the Keep Top Rows dialog box, enter 3 into the number of rows field, then click OK.

Select the Model column, then click Transform > Convert to List.

Click on the fx icon in the formula bar. Enter the following formula:

= Table.SelectRows(#"Changed Type", each List.Contains(Model, [Model]))
  • #”Changed Type”: The name of the step where the full data was last available
  • Model: The name of the previous step, which created the list
  • [Model]: The column name to filter by

The preview window displays the following:

After Dynamic filtering

We now have a dynamic filter list. When the source data changes, the list for the top 3 models is re-calculated. That dynamic list is used to filter the table.


In this post, we have seen how to filter by a list in Power Query. This was achieved using the List.Contains function which returns a True or False result for each row in a table. We also used the right-outer join of the merge transformation to create a filtering effect; this was significantly faster than List.Contains.

Finally, we explored how to filter column names and how to create a dynamic filter to display results for the top n elements.

Related Posts

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

4 thoughts on “How to filter by a list in Power Query (4 methods)

  1. Bill Botzong says:

    Once again, Mark, a clear, concise, and very useful tutorial. Thanks so much!

    If I have an unsolvable problem, could I get you to give me a suggestion? If so, how?

    Best regards!

  2. Ryan O says:

    I am trying to recreate a Power Query set of steps that a previous employee did and have been looking for what data was filtered from the data. Is there any way to determine what has been filtered out of the data?

Leave a Reply

Your email address will not be published. Required fields are marked *