Really like the table contains version, had an experiment ; this is add col but will work as filter ; = let pc = { {"Jeans", "Blue"}, { "Shirts", "Red"}} in Table.AddColumn( Source, "N", each List.Accumulate( pc , false, (s,c)=>s or c {0} = [Product] and c {1} = [Color] ))
Very good Rick. I have been doing this with merging which is powerful but limits me to similar combinations. The technique shown here with Table.Contains will let me do the equivalent of filter for: blue socks or yellow jeans with pockets or green jeans. So much better! I’ll definitely need List.Buffer though…
GReat :) Perfect Even :) I do however have a question. I know I can filter a Table by a List of Values, but from what I see it could be possible to filter Table A, by Table B, Can you help out figuring out the code? Ok after a bit of playing with some most basic tables: = Table.SelectRows( Custom4, each Table.Contains(Table4,[[AmountOfSales],[DateOfShipment]])) This is amazing!!! Recently Ihad to filter a Table by multiple Lists and I was going creazy! Thank you for showing Table.Contains function!!! Wow.
Good question. I wouldn’t be surprised a merge is quicker. But with several if conditions your approach could result in a lot of merge tables. One can only test to see the performance difference. May be different case to case.
Great video Rick. Question about the returned data. Instead of using a static value like "Sale", is it possible to return a value from a column in the ProductAndColor table?
Hi Tyonk, I don't fully grasp your situation and desired outcome. The ProductAndColor table I used was meant as a placeholder to filter the main table. What kind of result are you looking for?
hi there, Could please help me to solve this, I have simple list Source = {a, b ,c , d}, when I use this List.Transform( Source, each [_=List.PositionOf(Source, _)]) it create list of record but every record showing filed name "_" , _=0, _=1 etc.. instead of A=0, B=1 etc..
Sir Rick, "enter data" feature is probably not available in excel power query,,I mean it does come with power bi....but not other way round. The only way I do this is by importing another table with item on discount and then use drill down...later if my team tells me to change the products on discount I only make changes in the table I created...something like that I do...
Hi! The feature is not natively available in Excel. If you want to have it : You can make it in power bi, copy paste it to excel power query. And then the functionality works to its full extend. Even editing the table. However, I only used this example to show how you can achieve it. You can also reference other excel based table. The example purely showed the syntax. Enjoy power query!
Jordaar chho tame bhai. Massively underrated 🙌
thank you kindly 😁😁
Really like the table contains version, had an experiment ; this is add col but will work as filter ;
= let pc = { {"Jeans", "Blue"}, { "Shirts", "Red"}}
in
Table.AddColumn( Source, "N",
each List.Accumulate( pc , false, (s,c)=>s or c {0} = [Product] and c {1} = [Color]
))
Your videos are brilliant. One of the best I have seen.
Thank you very much Pravin, made with pleasure!
Awesome tips and techniques! Thanks for sharing. Thumbs up!!
Happy to help!
Really it's very useful video 👏👏..Request you to pls upload more videos like this ..Thank you ❤❤❤
Very good Rick. I have been doing this with merging which is powerful but limits me to similar combinations. The technique shown here with Table.Contains will let me do the equivalent of filter for: blue socks or yellow jeans with pockets or green jeans. So much better! I’ll definitely need List.Buffer though…
Great, glad to hear that. And perhaps you can use Table.Buffer for the more complex option. I’m sure you knew that one 😁
Excellent video... there are a lot for learning in just a 10 minutes video.
Thank you so much . Awesome trick .
Wow, thanks micael, Appreciate it 😁
Thanks for this great video! It's very helpful. Happy New Year!
Thanks Rick for this excellent content :)
My pleasure, thanks NM!
Excellent! Thank you and from your explanation I learned additional things too!
Excellent video 👌
Thanks for sharing ❤
Great video, really simplifies power querying . That's just what I need
Enjoy!❤
Amazing! I enjoy your videos a lot. Thank you.
Super 👌 I really liked it
Uffff!!!! That is Amazing!!!!!!!!!.....
And for more on the List.Contains Syntax in M, check out: powerquery.how/list-contains/
Nice and simple, straight to the point! Thanks man! Subbed!
❤❤ thankks
hmmm, great thinking :)
Thx a lot
Very well explained. Good work 👍
Thanks Mark! 🙏 it’s fun how flexible power query is with some creativity
Wow!
Another great and useful video, thanks
Enjoy power query, it’s awesome!
GReat :)
Perfect Even :)
I do however have a question.
I know I can filter a Table by a List of Values,
but from what I see it could be possible to filter Table A, by Table B,
Can you help out figuring out the code?
Ok after a bit of playing with some most basic tables:
= Table.SelectRows( Custom4, each Table.Contains(Table4,[[AmountOfSales],[DateOfShipment]]))
This is amazing!!!
Recently Ihad to filter a Table by multiple Lists and I was going creazy!
Thank you for showing Table.Contains function!!!
Wow.
How can i substract value from first value in column and result substract from second row in same column to get 0
Thank you for sharing this, is there a way to track values changes after refresh a power query in excel
I don't believe there's an easy way. You could use 2 separate tables and perhaps compare them when refreshing.
Can this be done with the filter function?
Excellent content 😊👍 just curious if it's faster then to use merge..I generally create a criteria table in such a scenario..
Good question. I wouldn’t be surprised a merge is quicker. But with several if conditions your approach could result in a lot of merge tables.
One can only test to see the performance difference. May be different case to case.
Great video Rick. Question about the returned data. Instead of using a static value like "Sale", is it possible to return a value from a column in the ProductAndColor table?
Hi Tyonk,
I don't fully grasp your situation and desired outcome. The ProductAndColor table I used was meant as a placeholder to filter the main table. What kind of result are you looking for?
Are there any faster alternatives? List.Contains is quite slow for big tables :(
You can wrap your list for the first argument in a List.Buffer function. This makes power bi hold the values in memory and should speed up your query.
hi there, Could please help me to solve this, I have simple list Source = {a, b ,c , d}, when I use this List.Transform( Source, each [_=List.PositionOf(Source, _)]) it create list of record but every record showing filed name "_" , _=0, _=1 etc.. instead of A=0, B=1 etc..
Sir Rick, "enter data" feature is probably not available in excel power query,,I mean it does come with power bi....but not other way round. The only way I do this is by importing another table with item on discount and then use drill down...later if my team tells me to change the products on discount I only make changes in the table I created...something like that I do...
Hi!
The feature is not natively available in Excel. If you want to have it :
You can make it in power bi, copy paste it to excel power query. And then the functionality works to its full extend. Even editing the table.
However, I only used this example to show how you can achieve it. You can also reference other excel based table.
The example purely showed the syntax. Enjoy power query!
@@BIGorilla I agree. And awesome video..cheers
Is there a way to do CTE recursive in PQ ?
What exactly does it do? And what’s the purpose?
@@BIGorilla The're used to query hierarchical data, say: employee_Id/manager_id or parent_node/leaf_node.
@@mattmatt245 I’m not sure how it behaves on data. I’d need a better grasp to know how I can do it in PQ
@@BIGorilla Typical example would be to find the number of direct and indirect reports for each manager. Two columns: Employee_id/Manager_id
How to use not in?