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

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post★
exceloffthegrid.com/power-que...
★ 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 ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel

[@YvesAustin]

Mark, simple and super effective. Thanks!!

[@ziggle314]

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

[@ExcelOffTheGrid]

I purposefully try to provide a progression with different techniques which hopefully help people to understand how things fit together. So I'm glad that comes across.

[@jeremynorbury]

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

[@ExcelOffTheGrid]

Merging is a simple SQL join, so is more likely to fold than List.Contains.

[@jeremynorbury]

@@ExcelOffTheGrid List.contains certainly folds on SQL server and OData. The additional advantage is that you can combine a non- folding source for the list (like an excel query of a table) and the SQL continues to fold.

[@Picla_Peremohy]

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

[@ExcelOffTheGrid]

I share what I know (and discover) and am always pleased that it helps other people to save time in their jobs.

[@Oprimaita]

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

[@ExcelOffTheGrid]

Glad it was helpful!

[@kebincui]

Super.Very well explained.👍👍

[@ExcelOffTheGrid]

As always Kebin - I appreciate your feedback and support 😁

[@chilaw2004]

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

[@shaunwilliams934]

Many Thanks. This really helped me

[@ExcelOffTheGrid]

Great news. 😁

[@mohammedelsakally540]

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

[@ExcelOffTheGrid]

Much appreciated!

[@edgarscheffrahn]

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

[@ExcelOffTheGrid]

You're very welcome!

[@ES-IvanCortinas]

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

[@peterfj6218]

Great work. Very well explained.

[@ExcelOffTheGrid]

Thanks Peter.

[@FRANKWHITE1996]

Thanks for sharing ❤

[@ExcelOffTheGrid]

You’re welcome. 👍

[@joaquimcosta952]

It's the best tutorial about this subject..

[@ExcelOffTheGrid]

Thank you 😁

[@Sumanth1601]

Excellent video as usual.

[@ExcelOffTheGrid]

Thanks Samantha 😁

[@nenadmomir]

nice! thank you Sir!

[@ExcelOffTheGrid]

You’re welcome.

[@martyc5674]

Good stuff Mark 👍

[@ExcelOffTheGrid]

Thanks Martin 👍

[@chriswall4795]

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.

[@kailash680]

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

[@user-ni9sj8gv2o]

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

[@ExcelOffTheGrid]

Thanks Tiffany - hold tight, there is more coming. Check out my previous videos for a lot of Power Query goodness too.

[@willm7994]

Amazing 🤩

[@ExcelOffTheGrid]

Thanks 🤗

[@alperkins66]

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.

[@alperkins66]

For some reason I have not had this problem again, i.e., my list is pulling (filtering) values with no further problem. Must be some User error.

[@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

[@vinhwizard]

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

[@ExcelOffTheGrid]

The Merge method is the fastest.

[@vinhwizard]

@@ExcelOffTheGrid I mean that the merge method makes the query slowly when run the big data. Is it right? I think that

[@user-lj4kp7fy1e]

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

[@ExcelOffTheGrid]

Rather than List.Contains use Text.Contains: = Table.SelectRows(#"Changed Type", each Text.Contains([Item],"C")) You can use an optional value to ignore case too = Table.SelectRows(#"Changed Type", each Text.Contains([Item],"C", Comparer.OrdinalIgnoreCase))

[@avmstephen9716]

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.

[@peterfj6218]

When I use that formula for filtering a dataset from a list, it wont recognize [Item] and therefore dont work. But if I use [#"Item"] it works. Do you know why that is?

[@yesmayhem]

Somehow, when I attempt the merge it filters out everything in the table that is not in the filter list. Does the list have to contain the exact matches in the table and nothing more? My table is huge.

[@andreawey7419]

I have a query with 900k results and i know that there is a specific item, but It is not shown in Theo filter check/uncheck list... Why so?

[@ExcelOffTheGrid]

Because Power Query only shows 1000 row preview of the data so that it can run efficiently. You can always click on the Load More link at the bottom of the pop-up.

[@user-oe2my3wx3d]

I have a question and if I need to chain several filters, how would it be?

[@ExcelOffTheGrid]

I would use a List.Accumulate function looping over a Grouped table of Columns and Values. It would be tough, be definitely do-able.

[@user-oe2my3wx3d]

@@ExcelOffTheGrid I have a question and if I want to filter with words that are in a text, what would the formula be?

[@954giggles]

What if you want to filter by three lists ? 3:16

[@user-gd3px5si1d]

Great, thanks!! What if in the list of columns I get I want to filter the values that do not contain, as example, "ColumnXXX" ? Many thanks if you can help