The IN Operator in Power Query

  Рет қаралды 8,454

BI Gorilla

BI Gorilla

Күн бұрын

Пікірлер: 55
@SMITDEDHIA96
@SMITDEDHIA96 2 жыл бұрын
Jordaar chho tame bhai. Massively underrated 🙌
@BIGorilla
@BIGorilla 2 жыл бұрын
thank you kindly 😁😁
@pravinshingadia7337
@pravinshingadia7337 2 жыл бұрын
Your videos are brilliant. One of the best I have seen.
@BIGorilla
@BIGorilla 2 жыл бұрын
Thank you very much Pravin, made with pleasure!
@nareshch1989
@nareshch1989 2 жыл бұрын
Really it's very useful video 👏👏..Request you to pls upload more videos like this ..Thank you ❤❤❤
@ВиталийШубин-к9э
@ВиталийШубин-к9э Жыл бұрын
Thanks for this great video! It's very helpful. Happy New Year!
@zahoorsarbandi2982
@zahoorsarbandi2982 2 жыл бұрын
Excellent video... there are a lot for learning in just a 10 minutes video.
@wayneedmondson1065
@wayneedmondson1065 2 жыл бұрын
Awesome tips and techniques! Thanks for sharing. Thumbs up!!
@BIGorilla
@BIGorilla 2 жыл бұрын
Happy to help!
@YTFC2008
@YTFC2008 Жыл бұрын
Excellent! Thank you and from your explanation I learned additional things too!
@PHILIPROYBENEDICTLOUIS
@PHILIPROYBENEDICTLOUIS 2 жыл бұрын
Excellent video 👌
@ExcelOffTheGrid
@ExcelOffTheGrid 2 жыл бұрын
Very well explained. Good work 👍
@BIGorilla
@BIGorilla 2 жыл бұрын
Thanks Mark! 🙏 it’s fun how flexible power query is with some creativity
@orleanCosta
@orleanCosta Жыл бұрын
Amazing! I enjoy your videos a lot. Thank you.
@Jamieemmart
@Jamieemmart 2 жыл бұрын
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…
@BIGorilla
@BIGorilla 2 жыл бұрын
Great, glad to hear that. And perhaps you can use Table.Buffer for the more complex option. I’m sure you knew that one 😁
@decentmendreams
@decentmendreams 2 жыл бұрын
Thank you so much . Awesome trick .
@BIGorilla
@BIGorilla 2 жыл бұрын
Wow, thanks micael, Appreciate it 😁
@NMVJ
@NMVJ 2 жыл бұрын
Thanks Rick for this excellent content :)
@BIGorilla
@BIGorilla 2 жыл бұрын
My pleasure, thanks NM!
@bagnon
@bagnon 2 жыл бұрын
Can this be done with the filter function?
@skimpylemon8034
@skimpylemon8034 2 жыл бұрын
Nice and simple, straight to the point! Thanks man! Subbed!
@BIGorilla
@BIGorilla 2 жыл бұрын
❤❤ thankks
@dicky4780
@dicky4780 2 жыл бұрын
Great video, really simplifies power querying . That's just what I need
@BIGorilla
@BIGorilla 2 жыл бұрын
Enjoy!❤
@williamarthur4801
@williamarthur4801 3 ай бұрын
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] ))
@FRANKWHITE1996
@FRANKWHITE1996 2 жыл бұрын
Thanks for sharing ❤
@Faisal63045
@Faisal63045 2 жыл бұрын
Thank you for sharing this, is there a way to track values changes after refresh a power query in excel
@BIGorilla
@BIGorilla 2 жыл бұрын
I don't believe there's an easy way. You could use 2 separate tables and perhaps compare them when refreshing.
@muralidharannatarajan4251
@muralidharannatarajan4251 Жыл бұрын
Super 👌 I really liked it
@roywilson9580
@roywilson9580 2 жыл бұрын
Another great and useful video, thanks
@BIGorilla
@BIGorilla 2 жыл бұрын
Enjoy power query, it’s awesome!
@rubenmunozverdu7528
@rubenmunozverdu7528 2 жыл бұрын
Are there any faster alternatives? List.Contains is quite slow for big tables :(
@BIGorilla
@BIGorilla 2 жыл бұрын
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.
@cesarsaldana3429
@cesarsaldana3429 8 ай бұрын
Uffff!!!! That is Amazing!!!!!!!!!.....
@Sumanth1601
@Sumanth1601 2 жыл бұрын
Excellent content 😊👍 just curious if it's faster then to use merge..I generally create a criteria table in such a scenario..
@BIGorilla
@BIGorilla 2 жыл бұрын
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.
@toynkaccounting3501
@toynkaccounting3501 2 жыл бұрын
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?
@BIGorilla
@BIGorilla 2 жыл бұрын
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?
@chengwang411
@chengwang411 Жыл бұрын
hmmm, great thinking :)
@SiraEkabutPlus
@SiraEkabutPlus Жыл бұрын
Thx a lot
@mattmatt245
@mattmatt245 2 жыл бұрын
Is there a way to do CTE recursive in PQ ?
@BIGorilla
@BIGorilla 2 жыл бұрын
What exactly does it do? And what’s the purpose?
@mattmatt245
@mattmatt245 2 жыл бұрын
@@BIGorilla The're used to query hierarchical data, say: employee_Id/manager_id or parent_node/leaf_node.
@BIGorilla
@BIGorilla 2 жыл бұрын
@@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
@mattmatt245
@mattmatt245 2 жыл бұрын
@@BIGorilla Typical example would be to find the number of direct and indirect reports for each manager. Two columns: Employee_id/Manager_id
@ExcelInstructor
@ExcelInstructor Жыл бұрын
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.
@vashisht1
@vashisht1 2 жыл бұрын
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...
@BIGorilla
@BIGorilla 2 жыл бұрын
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!
@vashisht1
@vashisht1 2 жыл бұрын
@@BIGorilla I agree. And awesome video..cheers
@kubansandybay496
@kubansandybay496 Жыл бұрын
Wow!
@BIGorilla
@BIGorilla Жыл бұрын
And for more on the List.Contains Syntax in M, check out: powerquery.how/list-contains/
@SamehRSameh
@SamehRSameh 3 ай бұрын
How can i substract value from first value in column and result substract from second row in same column to get 0
@rashidkhan8161
@rashidkhan8161 2 жыл бұрын
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..
@littlekidscartoonplanet
@littlekidscartoonplanet Жыл бұрын
How to use not in?
Extract Dates From Text Strings in Power Query
9:34
BI Gorilla
Рет қаралды 16 М.
Understanding List Sort in Power Query
16:47
BI Gorilla
Рет қаралды 8 М.
IL'HAN - Qalqam | Official Music Video
03:17
Ilhan Ihsanov
Рет қаралды 651 М.
REAL or FAKE? #beatbox #tiktok
01:03
BeatboxJCOP
Рет қаралды 17 МЛН
How Strong Is Tape?
00:24
Stokes Twins
Рет қаралды 86 МЛН
Advanced Unpivoting Tricks in Power Query
30:19
Goodly
Рет қаралды 25 М.
How to easily automate boring Excel tasks with Power Query!
17:10
MyOnlineTrainingHub
Рет қаралды 1,6 МЛН
Transform Column Names in Bulk in Power Query
19:23
BI Gorilla
Рет қаралды 37 М.
Understanding the IN operator in DAX
15:48
SQLBI
Рет қаралды 7 М.
List.Generate in Power Query - Tutorial with Easy Examples
22:56
The Definitive Guide to Power Query M - What to expect?
27:13
BI Gorilla
Рет қаралды 5 М.
List.Accumulate in Power Query with Practical Examples
27:26
IL'HAN - Qalqam | Official Music Video
03:17
Ilhan Ihsanov
Рет қаралды 651 М.