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.
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!!!
Saved the best for last, meaning the technique of referencing a prior step. This example illustrates the technique well because it produces a list (of the top three), then by pointing to the Change-Type step, the query becomes a table again. In the past, I've grouped in a separate query, then went back to the first query and merged against the 2nd, to include only top 3.
@@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.
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.
oh my. All this time I was using left-outer, then filtering out null results. Excited to instead use right-outer, then delete column. Plus, of course, the list filter technique avoids the merge completely.
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
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.
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))
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.
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.
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?
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.
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
I like the way you cover multiple options for the same task. Nice job.
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.
You always pick worthwhile ideas and, where possible, multiple options to achieve the goal.
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!!!
Thank you. I appreciate that feedback. 😁
Saved the best for last, meaning the technique of referencing a prior step. This example illustrates the technique well because it produces a list (of the top three), then by pointing to the Change-Type step, the query becomes a table again. In the past, I've grouped in a separate query, then went back to the first query and merged against the 2nd, to include only top 3.
Superb tutorial, as always, complete, going deeper when it is necessary to go deeper and explained in great detail. Thank you Mark.
This tutorial is just perfect. All Your explanations makes sense to me. It’s very clear. Thanks a lot.
Glad it was helpful!
Another valuable video, thank you very much. We will continue supporting you for your continues support and valuable information.
Much appreciated!
Tremendous presentation Mark. Always have a wealth of actionable knowledge. Cheers
I share what I know (and discover) and am always pleased that it helps other people to save time in their jobs.
Many many thanks for this excellent video: just what i needed!
You're very welcome!
Mark, simple and super effective. Thanks!!
List.Contains also plays nicely with query folding...and is executed on the server side for a DB connector.
Merging is a simple SQL join, so is more likely to fold than List.Contains.
@@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.
Many Thanks. This really helped me
Great news. 😁
A very nice lecture. Thank you.
Glad it was helpful! 😁
Super.Very well explained.👍👍
As always Kebin - I appreciate your feedback and support 😁
Thanks Mark, Great video
Glad it was helpful!
Great work. Very well explained.
Thanks Peter.
Helpful. Thanks!
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.
It's the best tutorial about this subject..
Thank you 😁
Excellent video as usual.
Thanks Samantha 😁
Thank you for your video! It really helped a lot!👌
Glad to hear that! 😁
Thanks for sharing ❤
You’re welcome. 👍
Good stuff Mark 👍
Thanks Martin 👍
superb video, thank you.. auto subscribe, more tips and trick for power query please. Love it!!
Thanks Tiffany - hold tight, there is more coming. Check out my previous videos for a lot of Power Query goodness too.
nice! thank you Sir!
You’re welcome.
oh my. All this time I was using left-outer, then filtering out null results. Excited to instead use right-outer, then delete column. Plus, of course, the list filter technique avoids the merge completely.
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
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.
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.
Great!!, and the second method makes slower, is it right?
The Merge method is the fastest.
@@ExcelOffTheGrid I mean that the merge method makes the query slowly when run the big data. Is it right? I think that
Hey, I would like to filter by the same way but only with part of the text string available in the filter list
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))
Is there a way to do the merge method but inverse (filter for does not contain)? Similar to "each not List.Contains"
it is easier to follow if you show the right pane always
Amazing 🤩
Thanks 🤗
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.
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.
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?
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?
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.
I have a question and if I need to chain several filters, how would it be?
I would use a List.Accumulate function looping over a Grouped table of Columns and Values. It would be tough, be definitely do-able.
@@ExcelOffTheGrid I have a question and if I want to filter with words that are in a text, what would the formula be?
What if you want to filter by three lists ? 3:16
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