4 METHODS to Filter by a List in Power Query

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

Рет қаралды 31,065

Күн бұрын

★ 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

Пікірлер: 58

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

@IvanCortinas_ES Жыл бұрын

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

@chilaw2004 Жыл бұрын

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

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

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

@YvesAustin 2 ай бұрын

Mark, simple and super effective. Thanks!!

@mohammedelsakally540 9 ай бұрын

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

@ExcelOffTheGrid 9 ай бұрын

Much appreciated!

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

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

@peterfj6218 Жыл бұрын

Great work. Very well explained.

@ExcelOffTheGrid Жыл бұрын

Thanks Peter.

@edgarscheffrahn Жыл бұрын

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

@ExcelOffTheGrid Жыл бұрын

You're very welcome!

@kebincui Жыл бұрын

Super.Very well explained.👍👍

@ExcelOffTheGrid Жыл бұрын

As always Kebin - I appreciate your feedback and support 😁

@Ghost-kx1ss 24 күн бұрын

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

@ExcelOffTheGrid 20 күн бұрын

Glad to hear that! 😁

@FRANKWHITE1996 Жыл бұрын

Thanks for sharing ❤

@ExcelOffTheGrid Жыл бұрын

You’re welcome. 👍

@Sumanth1601 Жыл бұрын

Excellent video as usual.

@ExcelOffTheGrid Жыл бұрын

Thanks Samantha 😁

@shaunwilliams934 4 ай бұрын

Many Thanks. This really helped me

@ExcelOffTheGrid 4 ай бұрын

Great news. 😁

@martyc5674 Жыл бұрын

Good stuff Mark 👍

@ExcelOffTheGrid Жыл бұрын

Thanks Martin 👍

@nenadmomir 10 ай бұрын

nice! thank you Sir!

@ExcelOffTheGrid 10 ай бұрын

You’re welcome.

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

@joaquimcosta952 6 ай бұрын

It's the best tutorial about this subject..

@ExcelOffTheGrid 6 ай бұрын

Thank you 😁

@willm7994 9 ай бұрын

Amazing 🤩

@ExcelOffTheGrid 9 ай бұрын

Thanks 🤗

@alperkins66 3 ай бұрын

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 3 ай бұрын

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 8 ай бұрын

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

@ExcelOffTheGrid 8 ай бұрын

The Merge method is the fastest.

@vinhwizard 8 ай бұрын

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

@kailash680 8 ай бұрын

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

@user-lj4kp7fy1e 11 ай бұрын

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

@ExcelOffTheGrid 11 ай бұрын

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 11 ай бұрын

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 9 ай бұрын

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 6 ай бұрын

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 6 ай бұрын

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 9 ай бұрын

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

@ExcelOffTheGrid 8 ай бұрын

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 8 ай бұрын

@@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 2 ай бұрын

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