4 METHODS to Filter by a List in Power Query | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Check out the blog post★

★ About this video ★
Filtering is a common task in Power Query, but usually this is based on a single value or a limited number of known values. What happens if we want to be more dynamic? What if we want to base our filter on a list of items? In this video, we show you FOUR methods of doing this.

0:00 Introduction
0:43 Example Data
1:25 Filter by a list
6:30 Merge: a faster alternative
8:06 Filter columns by a list
9:45 Dynamic filtering
12:32 Conclusion

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[zbatevp-vlogs]

This is gold, because you are explaining the concept of the function and its parameters. keep doing what your doing Sir - you are AMAZING. Thank you!!!

[ziggle]

I like the way you cover multiple options for the same task. Nice job.

[chilaw]

You always pick worthwhile ideas and, where possible, multiple options to achieve the goal.

[IvanCortinas_ES]

Superb tutorial, as always, complete, going deeper when it is necessary to go deeper and explained in great detail. Thank you Mark.

[Oprimaita]

This tutorial is just perfect. All Your explanations makes sense to me. It’s very clear. Thanks a lot.

[Picla_Peremohy]

Tremendous presentation Mark. Always have a wealth of actionable knowledge. Cheers

[mohammedelsakally]

Another valuable video, thank you very much. We will continue supporting you for your continues support and valuable information.

[jeremynorbury]

List.Contains also plays nicely with query folding...and is executed on the server side for a DB connector.

[YvesAustin]

Mark, simple and super effective. Thanks!!

[edgarscheffrahn]

Many many thanks for this excellent video: just what i needed!

[alperkins]

A wonderful lesson!! Mark. Question: How should one deal with a list when the values are numeric? Or is there a way to have a numeric list? I had to change out my fact table to text so it would match the list and even work.

[joaquimcosta]

It's the best tutorial about this subject..

[Ghost-kxss]

Thank you for your video! It really helped a lot!👌

[chriswall]

Really interesting - thinking of ways we might use this to streamline our processing ... already worked out the merge approach. Another thought - would be good to adjust the top 3 to top n by passing a number to PQ and using a parameter/variable in the code.

[TiffanyYoung-zs]

superb video, thank you.. auto subscribe, more tips and trick for power query please. Love it!!

[MarcoMagal]

Hi Mark,
That's a great explanation.
It is possible to do the same filter when you want to filter records that start only with some chars?.

ex : #"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each Text.StartsWith([Territory], "PT_PH_SM_")),

I have try but only work if I have the complet value.

//#"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each List.Contains(Din_Filter_Territory, [Territory])=true),

Kind regards,
Marco

[kailash]

it is easier to follow if you show the right pane always

[vinhwizard]

Great!!, and the second method makes slower, is it right?

[UjjawalKumar-ud]

Hey, I would like to filter by the same way but only with part of the text string available in the filter list

[avmstephen]

i am in need some help in power query to do following task.can you suggest some solution.
- some rows to be filterout based on some condition(all zero value) in a column
-ranking to be applied in new column based on above filtered column.
-All filtered rows to be restored, so that i will not have any ranking in the filtered rows.