3 ways of filtering data using dynamic lists - Power Query (Excel and Power BI)

Follow me on Twitter: @GracielaPowerGI Welcome!
This video shows how to filter data using dynamic lists using Power Query.
It shows 3 ways:
1. Filter using Exact match and List.Contains function 0:00 - 4:42
2. Filter using Exact match and Merge functionality 4:43 - 8:10
3. Filter using "Contains" logic and List.Accumulate function 8:11 - 12:12

Funtions used:

List.Contains(ListQuery, [Customer])
List.Accumulate(FilterList, null, (state, current) =[greater than] if Text.Contains([Customer], current) then current else state)

Article referenced in the video:

Sample file can be found here:

[PowerGI]

This video shows how to filter data using dynamic lists using Power Query.
It shows 3 ways:
1. Filter using Exact match and List.Contains function 0:00 - 4:47
2. Filter using Exact match and Merge functionality 4:48 - 8:10
3. Filter using "Contains" logic and List.Accumulate function 8:11 - 12:12

[fahimanjaria]

The third method is really amazing. I had a similar issue some years back, I wish this video was available back then.

[Inthaphong]

I wonder for long time how to filter with list. This video is key for me.

[julie_chen]

Picked up one more know-how tip. Inserting the list into steps. 🙏🙏🙏🙏

[yolanizitumane]

Oh my word I have looking for a video on dynamic filtering. Thanks you so much. Excellent video.

[dso]

Thank you very much!
Your video helped me a lot!!!
You did an excellent job!!!
A huge hug from Brazil!

[pieterkaruniadeo]

Hello. Let say I want a reverse. If contain nancy then need to null, how is it ?

List.Accumulate(FilterList, null, (state, current) =[greater than] if Text.Contains([Customer], current) then null else current) ?

[jhsimbana]

The List.Buffer function improved performance, but collapsing the column caused a delay in query loading. Any help with that? Thanks in advance.

[tiste_sg]

Great video, thanks! Which method is the best performance-wise? I've been using method 2 for a few years, and I'm intrigued about whether I should use method 1 or 3 going forward?

[akask]

Thank you so much - this is excellent and much appreciated

[soniaribeiro]

This is a great lesson - I have used the first way numerous times already. Thank you very much!

Q: Is it possible to filter a date column to find values greater or equal to a date in a list i.e. not an exact match?

[allonewordlowercase]

This video is wonderful. Can I ask, which method out of 1 and 2 ensures the best performance? I am working with hundreds and in some cases thousands of lines of data

[idng]

What could I do if the connection to the table in the spreadsheet is not reflecting the items I select in power query?