The IN Operator in Power Query
ฝัง
- เผยแพร่เมื่อ 28 มิ.ย. 2024
- Looking for the IN Operator equivalent in Power Query? This video shows you exactly what you need.
WRITTEN ARTICLE:
gorilla.bi/power-query/in-ope...
CONTENT
00:00 Introduction
01:41 IN operator single column
03:54 Reference Table Values
05:34 IN Operator multiple columns
ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
th-cam.com/users/bigorilla?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#INoperator #powerquery #bigorilla
![[UNCUT] ฝันรักห้วงนิทรา | EP.8 (4/4)](http://i.ytimg.com/vi/RDYJYiDwsj0/mqdefault.jpg)
![LISA - ROCKSTAR (Official Music Video) REACTION [SHE'S BACK!]](http://i.ytimg.com/vi/QBxE30vavMQ/mqdefault.jpg)
Jordaar chho tame bhai. Massively underrated 🙌
thank you kindly 😁😁
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 ❤❤❤
Amazing! I enjoy your videos a lot. Thank you.
Excellent video... there are a lot for learning in just a 10 minutes video.
Thanks for this great video! It's very helpful. Happy New Year!
Thanks Rick for this excellent content :)
My pleasure, thanks NM!
Thank you so much . Awesome trick .
Wow, thanks micael, Appreciate it 😁
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! Thank you and from your explanation I learned additional things too!
Nice and simple, straight to the point! Thanks man! Subbed!
❤❤ thankks
Excellent video 👌
Great video, really simplifies power querying . That's just what I need
Enjoy!❤
Thanks for sharing ❤
And for more on the List.Contains Syntax in M, check out: powerquery.how/list-contains/
Thx a lot
Another great and useful video, thanks
Enjoy power query, it’s awesome!
Very well explained. Good work 👍
Thanks Mark! 🙏 it’s fun how flexible power query is with some creativity
Uffff!!!! That is Amazing!!!!!!!!!.....
Super 👌 I really liked it
hmmm, great thinking :)
Wow!
Can this be done with the filter function?
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?
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.
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.
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.
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.
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
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..
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?