Excel Unplugged

Filtering a Table to a List of Values in Power Query or Power BI

Filter a Table to a Dynamic List of Values

Utilizing Power Query Lists Series – Part 1/3

Lists are (in my humble opinion) the most underrated output of Power Query. So rarely do we use them on purpose, and yet, we use them all the time since Power Query writes a new line of M code every time you click any command in the graphical user interface (GUI). Very often, those lines of code will contain Lists (most Common steps to do so are Changed Type steps). You should always keep a lookout for the curly braces as those indicate a list of values being passed to an M function. This post will focus on Filtering a Table to a List of Values, but in the entire three posts series, we will look at how we can use lists to do the following:

  1. Dynamically Filtering a Table to a List of Values (This post)
  2. Changing a Table of Attributes and Values to retain only Top 10 Attributes (by values) and turns the rest to Others (URL coming April 14th, 2020)
  3. Dynamically retaining only columns that contain Actual amounts from a Table (URL coming April 28th, 2020)

You can get the Workbook here and follow along.

The Workbook

We start with two Tables. Table AllCountries contains a list of Countries plus some demographic data and looks like this.

Power Query Power BI Filtering Table to Dynamic List Image 1

Table ListOfTen is just a random list of 10 countries from the main Table and looks something like this (the Id’s are calculated using RANDBETWEEN (in Office 365 with Dynamic Arrays there are far better ways to do this, but I used RANDBETWEEN so that all the steps apply to Excel 2010 and above)).

Power Query Power BI Filtering Table to Dynamic List Image 2

We get both tables into Power Query by selecting a single cell within the tables and clicking on Data>From Table/Range.

Power Query Power BI Filtering Table to Dynamic List Image 3

We should end up with something like this.

Power Query Power BI Filtering Table to Dynamic List Image 4

Now comes the important part. We must convert the list of ten countries into a list. We do this by selecting the Country column of the Table ListOfTen within the Power Query Editor and selecting the Transform>Convert to List command.

Power Query Power BI Filtering Table to Dynamic List Image 5

You should now see a noticeable change in the icon of the ListOfTen Query, suggesting that it is no longer returning a table but a list of items.

Power Query Power BI Filtering Table to Dynamic List Image 6

This means we now have a dynamic list of ten countries. The next step is to Filter a Table to a list that we just created.

Now we shift to the AllCountries Query and only select a few countries. The idea here is that we wish to get most of the code that we need for the next step.

Power Query Power BI Filtering Table to Dynamic List Image 7

You should now see something like this in the Formula Bar.

Power Query Power BI Filtering Table to Dynamic List Image 8

We will only need this part of the code

= Table.SelectRows(#"Replaced Errors", each

And delete the rest.

Now we will replace the deleted part with

(List.Contains(ListOfTen,[Country])=true ))

And we should see something like this

Power Query Power BI Filtering Table to Dynamic List Image 9

Here is the code of the entire step:

= Table.SelectRows(#"Replaced Errors", each (List.Contains(ListOfTen,[Country])=true ))

And voila, we now only see the countries from our list.

Power Query Power BI Filtering Table to Dynamic List Image 10

After loading, this is what you get.

Power Query Power BI Filtering Table to Dynamic List Image 11

I hope it’s clear that if we replaced the true with false, we would exclude all the countries in our list from the result, which is just brilliant.

Comments 6

  1. Michael V Bernot says:

    Thank you! I will definitely be using this technique for future Excel projects! I look forward to seeing the other planned posts! I have to agree with you about lists being underrated! My first exposure to this feature in PQ. You mentioned better ways with dynamic arrays. Something quick you could share? Thank you again! Thoroughly enjoyed that!!

    1. There is definitely a plan to produce the Dynamic Arrays solution too. Stay tuned to the blog 🙂

  2. Frank Byl says:

    Didn’t work for me. When I got to the step where I filtered out ten countries and went to copy the formula, my formula showed

    = Table.SelectRows(#”Changed Type”, each ([Country] = “Afghanistan” or [Country] = “Albania” or [Country]

    which is different from your which shows

    = Table.SelectRows(#”replaced errors”, each ([Country] = “Afghanistan” or [Country] = “Albania” or [Country]

    Not sure what I am doing wrong

    1. The only real difference there is the name of the previous step which only means you have an older version of Power Query which does not automatically give the Change Type step.

  3. Michael V Bernot says:

    Does List.Contains only work with text data or would this also work with numbers. Thank you for the post! Looking forward to the others in the series!!

    1. With the ability to switch between text and numbers with Number.From and Text.From it really doesn’t matter :).

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: