Filtering a Table to a Dynamic List of Values using Power Query and Power BI

This video will show you how to filter a column in a table to a list of values. The greatest thing when using this method is that there are endless ways to get the list that serves as your filter, from top N to percentiles and so on. The possibilities are endless, but in the end, it all rests on the technique described in this video. It should be noted that the same thing can be achieved by merging two queries and choosing the Inner Join as the join type, but both techniques have their place as they do represent different steps and different resource needs.

This tutorial was made using Excel 365 but is applicable to older versions of Excel all the way back to Excel 2010.

Chapters:
0:00​ Excel Olympics Animation
0:08​ Intro
0:21 Explanation
0:46​ Filtering a Table to a list of values
3:53​ Aditional options
5:05​ Outro


Gear Used:

US Links

EU Links



DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links that I provide I may receive a small commission with NO additional charge to you! Thank you for your support!

#Excel #ExcelOlympics #gasperkamensek

[EricHartwigExcelConsulting]

This is awesome and has some many different application for reducing or returning data based on criteria!
I love it! Great video!

[kebincui]

Thanks Gasper for sharing, very good tutorial

[lilyli]

This is what I've been looking for and it could be extended to a few column filters. Excellent, and thanks so so much!!!

[akhilmashood]

Really nice tip. Thank you for sharing.

[eladiohernandez]

Awesome tips. You've saved me a lot of time with this. You have earned a new follower.

[wayneedmondson]

Hi Gasper. Awesome!! Love this trick. Can be used in so many ways. Thanks for sharing :)) Thumbs up!!

[ikaraburc]

thank you very much brother. very good way.

[violetnha]

Awesome tip! Can't wait to use this!

[fajnefajne]

Excellent. Wonderful. Amazing. Great. Awesome. Brilliant. Splendid. !!!
I love this video. Thank you very much for sharing that amazing solution. :-)
I was just looking for a way to extract from the source only the selected rows rather than extracting all rows and then merging (inner joint) with the desired rows. It looks that this trick eliminates the time needed for extracting the whole table and it looks it extracts only the required rows from the source using the advantage of Native Query. ;-)

[afiqyahya]

What's different if i did not put "= true" after list.contains? It seems filter what i want though i did not put it.

[barttitulaerexcelbart]

Thanks Gasper, for showing this list syntax, but why can't you get the same result with just a merge?

[idng]

Hi! Is there a way to only filter based on values that were filtered in the dynamic list table?

[rakshitharamesh]

Thank you!
I want to know if it is possible to setup two such field list(Ex Country, Capital) with OR criteria.So the filter must apply either one of it changes

[haroonsheikh]

Is there a way to do this without an Exact match? Like in Excel if I were to filter and search one word everything containing that word would pop up. In Power Query, nothing would show up unless it was an exact match.

[jimfitch]

I use merge to filter one PQ table on another. Is this list technique faster than merge? Are there other benefits?

[NCRsNiPeR]

This is a great video that is exactly what I need however I am getting an error the says there is a cyclic reference during evaluation. Have you come across this or do you know how to fix it at all?

[TheRemyRomano]

Can we populate a list from a string of coma separated values, like from a Sharepoint list field? I want to use the fields of a Sharepoint list to contain the search criteria for a variety of columns. I need each customers filter criteria to be in its own record. I want to show a table of records that match the filter criteria and a set that don’t match.

[vazik_cze]

Hello, i was always using each not List.Contains instead of parameter "false". I wonder if there is any difference?

[enricomendiola]

Amazing video! I wonder if I have 4 sets of list criteria, filter using the every list then from that filtered list I will input a value in an added column say list1. Doing that process for all the 4 sets of list. Then all that 4 filtered list with some values will be merged into 1 table.

[pieterkaruniadeo]

How if we want to filter based on some keyword ? let's say only contain "a"