Filtering a Table to a List of Values in Power Query or Power BI
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:
- Dynamically Filtering a Table to a List of Values (This post)
- Changing a Table of Attributes and Values to retain only Top 10 Attributes (by values) and turns the rest to Others
- Dynamically retaining only columns that contain Actual amounts from a Table
You can get the Workbook here and follow along.
If you prefer learning through video, you can watch this video or view the video embedded at the end of this article.
The Workbook
We start with two Tables. Table AllCountries contains a list of Countries plus some demographic data and looks like this.
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)).
We get both tables into Power Query by selecting a single cell within the tables and clicking on Data>From Table/Range.
Consequently, we should end up with something like this.
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.
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.
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.
You should now see something like this in the Formula Bar.
We will only need this part of the code
= Table.SelectRows(#"Replaced Errors", eachAnd delete the rest.
Now we will replace the deleted part with
(List.Contains(ListOfTen,[Country])=true ))And we should see something like this
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.
After loading, this is what you get.
To clarify, if we replaced the true with false, we would exclude all the countries in our list from the result, which is just brilliant.
In conclusion, this was sums up Part 1 of the series. Stay tuned for the following parts!
Watch the tutorial
Here is a YouTube video explaining all the steps in detail.
Comments 42
Leave a Reply
Excel Unplugged acknowledgements
Donate
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!!
There is definitely a plan to produce the Dynamic Arrays solution too. Stay tuned to the blog 🙂
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
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.
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!!
With the ability to switch between text and numbers with Number.From and Text.From it really doesn’t matter :).
Great post! I will use this technicque to solve bank account issue.
Greetings from Colombia
Thanks, Fernando. Glad to hear you find the content usefull.
Excellent, this works great. I have been looking for this exact solution especially with the option to include/exclude the list list with the boolean. Will implement this as an additional parameter.
Great to hear that Terry.
What if we need to do the opposite? Like to ignore/ filter out the countries? Is there a opposite of list.contain like list.notcontain?
Sure there is. Instead of List.Contains(…=trueyou use List.Contains(…=false)
Hi,
How can i select reow different from liste, i try this but it doesn’t work?
= Table.SelectRows(#”Colonne conditionnelle ajoutée2″, each [Date de base] #”date du jour -30″=true)
Can you help me please?
thnks
Hi,
you only use =true at the end when comparing to a list of values and if that is the case, so if your #”date du jour -30 step” returns a list then you need the List.Contains function and it should read something like
List.Contains(#”date du jour -30″, [Date de base] )=true
Thank you, this works brilliantly!
Hi Gasper. This is awesome! I’ve been looking for just how to do this. Thanks so much. Also, I discovered that you don’t need the parenthesis around the List.Contains() function. This will work:
= Table.SelectRows(#”Changed Type”, each List.Contains(ListOfTen,[Country])=true)
as opposed to this:
= Table.SelectRows(#”Changed Type”, each (List.Contains(ListOfTen,[Country])=true))
I guess it doesn’t really matter to include them, just not needed.
Really glad I found your YouTube channel, newsletter and website. Looking forward to more!
Cheers :))
Nice Catch, Wayne. They are mostly there for readability purposes.
Hi Gasper,
I guess you can also do it by emitting ‘=true’ since the default condition of List.Contains is already as if it is true and if you want to exclude it just use ‘not’ prior instead ‘=false’
Great content!
All True Excel Learner. Thanks for pointing that out!
This great and close to what I was looking for. I need to filter for keywords within the text of the searched column. I applied your solution, but it appears that it only returns exact matches on the full text of the searched item and the keyword, so it doesn’t quite work for my scenario. Suggestions on how to alter this to work for searching for a keyword existing anywhere within the searched column?
Thanks!
Hi Donald. I plan to publish an article in the next week or so where will be discussing exactly that. It should also feature a YouTube video so it will be easier to follow along. I’ll let you know when I publish it.
Hi Donald and Gasper.. I had the same requirement and found this recent YT post by Oz which I think addresses your question: https://www.youtube.com/watch?app=desktop&v=ljvqbT-6SNs
In short, make a table of text to search for; load and only create connection; load main table that you want to search in; add custom column to the search in table consisting of the full lookup table; expand the custom column with the lookup table; add a new custom column with the following: Text.Contains([Column you want to Search in],[Column1 you want to search from]) and Text.Contains[Column you want to Search in],[Column2 you want to search from]). Resulting column will have TRUE / FALSE. Filter for all the TRUEs which will give you a Table of the items in the Search in column that match the text in the Search from column(s). Remove unneeded columns; close and load. It is a cool solution and will make more sense when you watch Oz’s video.. just 5 minutes. He calls it a Cross-Join. I’ve already successfully used the technique in a project. Looking forward to Gasper’s take on how to do it. Hope this helps! Good luck :))
Hi Gasper,
Thank you so much, it worked for me. Is there way to change the list so that everything comes in (essential to unfilter). I tried using * in the list but didn’t work.
Steve
Hi, Steve,
Basically, you filter by the list of everything and therefore returning everything.
The concern is that new items might be added to the column I am filtering. Then I need to check this each time to make sure the list includes all items in order to filter by everything. That’s why I was checking if there was an easier way, such as using *
How about providing a value you know will never exist and set up your filter to “Does not equal to” that value… This would in effect give you everything every time :).
Simple but brilliant. Thank you sir
Great post, thank you! I used this approach to filter the date column with reference to my list and it worked.
How can I edit the formula to still refer to the list but to be less than and equal to my list and not just equal to?
Hi Sonia. This would mean you start with a single value but have to create a list of values on the fly. It would make no sense to start with a list {1,5,9} if the condition is “equal to or less then”. Then that list would give you the same result as a single value – 9.
That’s a great post.
I want to do the same but with dates.
For Example I have two tables Sales and Order. I want to filter the sales table by Sales Date in such a way that the Sales Date >= Max Order Date.
I have created a list for max order date, and providing this to List.Contains is filtering sales table to have only one Sales date which is equal to max of order date, and i want sales date is on or after the max of order date.
I would really appreciate any help. thank you.
Hi,
If you want to have a greater than filter, it makes no sense to give a list of dates. If, for example, your list would contain Jan 31st, 2020, March 3rd, 2020, and July 7th, 2021, your filter should actually be Greater than Jan 31th 2020 as that one contains the other two… So what you would do is create a date filter “is after” and provide your earliest date dynamically.
I want to exclude the 30 days before today’s date on a large date list. I created the list with formula which indicates the dates to exclude I still have to put the formula in the filter to exclude them.
Thank you for your help
Hi Lagha,
please try with my answer provided to your first question.
Thank you Gasper!
Hi, this is a great technique. Just wondering if there was a limit to the size/number of rows allowed in the list?
Depends only on your version of Excel (32 bit or 64 bit) and the amount of RAM on your computer.
Thank you Gasper,
Do we have to add another List.Contains if there are multiple criteria.
If there is another List for CityName then do we have to add another List.contains ?
Something like
= Table.SelectRows(#”Changed Type”, each List.Contains(ListOfTen,[Country])=true OR List.Contains(CityName,[Capital])=true)
True. You could just do one list but that would not change the fact that you need two List.Contains statements.
Hi from Québec-Canada,
My name is Gilles, I was wondering if it was possible to do the same thing, but with a list of the countries I don’t want?
I have to run a report for Projects under construction, which has a lot of data.
I’ve been looking for a way to have a list of specific Project Number that I could remove.
Do you have a solution?
Merci
Gilles
Sure Thing. Everything else is the same but in the function part of List.Contains you say = false…
Hello! I used this on a table of 250,000 lines using a list of 17 different items as filter. The load time just explodes (not patient enough to wait for the load to complete, but it’s well over 5 minutes (it was around 30 seconds with the manual filter). Is there anything I can do to still use the list but reduce the load time?
Hi Laurence,
you could use Table.Buffer just before you do the “magic” step but other than that I’m afraid it will take more time as it’s iterating through the entire table.