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

Подписаться 27 тыс.

Просмотров 30 тыс.

50% 1

★ 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

Опубликовано:

23 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 56

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

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

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

@YvesAustin Месяц назад

Mark, simple and super effective. Thanks!!

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

@mohammedelsakally540 8 месяцев назад

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

@ExcelOffTheGrid 8 месяцев назад

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.

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

@shaunwilliams934 3 месяца назад

Many Thanks. This really helped me

@ExcelOffTheGrid 3 месяца назад

Great news. 😁

@peterfj6218 Год назад

Great work. Very well explained.

@ExcelOffTheGrid Год назад

Thanks Peter.

@FRANKWHITE1996 Год назад

Thanks for sharing ❤

@ExcelOffTheGrid Год назад

You’re welcome. 👍

@nenadmomir 9 месяцев назад

nice! thank you Sir!

@ExcelOffTheGrid 9 месяцев назад

You’re welcome.

@Sumanth1601 Год назад

Excellent video as usual.

@ExcelOffTheGrid Год назад

Thanks Samantha 😁

@martyc5674 Год назад

Good stuff Mark 👍

@ExcelOffTheGrid Год назад

Thanks Martin 👍

@joaquimcosta952 5 месяцев назад

It's the best tutorial about this subject..

@ExcelOffTheGrid 5 месяцев назад

Thank you 😁

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

@willm7994 8 месяцев назад

Amazing 🤩

@ExcelOffTheGrid 8 месяцев назад

Thanks 🤗

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

@kailash680 7 месяцев назад

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

@alperkins66 2 месяца назад

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 2 месяца назад

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 7 месяцев назад

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

@ExcelOffTheGrid 7 месяцев назад

The Merge method is the fastest.

@vinhwizard 7 месяцев назад

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

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

@user-lj4kp7fy1e 10 месяцев назад

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

@ExcelOffTheGrid 10 месяцев назад

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))

@andreawey7419 5 месяцев назад

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 5 месяцев назад

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.

@avmstephen9716 10 месяцев назад

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?

@954giggles Год назад

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

@user-oe2my3wx3d 8 месяцев назад

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

@ExcelOffTheGrid 7 месяцев назад

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 7 месяцев назад

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

@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