Exact & Approximate Match Lookup Formulas in Power Query. 4 Amazing Formulas. Excel Magic Trick 1747
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1747....
Where is the VLOOKUP or XLOOKUP function in Power Query? Learn how to perform Exact Match and Approximate Match Lookup in power query withfour different formulas:
1. (00:00) Intro
2. (02:07) Formula 1: Exact Match Lookup with Record.Field. Lookup Row and Then Column.
3. (05:11) Formula 2: Exact Match Lookup with List.PositionOf. Lookup Column and Then Row.
4. (08:12) Formula 3: Approximate Match Lookup with a Custom Function to lookup Rows and then Column. See the Table.SelectRows and List.Last functions
5. (11:20) Formula 4: Approximate Match Lookup using the let expression lookup Column and then Row. See the List.Select and List.Count function.
6. (15:40) Conclusion and call for comments
I did some timing on all formulas in the video and a few more. I times on an 8 million row SQL database. Here are results:
For Exact Match here are results:
1st "Table.RemoveColumns(
Table.Join(#""Changed Type"",""Product"",dProduct,""Product""),
{""Standard Cost"", ""Category""})" 1:18 for all 8 million rows
1st User Interface Merge 1:18 for all 8 million rows
2nd (Row and then Column) Record.Field(dProduct{[Product=[Product]]},"Retail Price") Loaded 200,000 rows in 1:54 minutes. Stopped at that point
2nd (Row and then Column) dProduct{[Product=[Product]]}[Retail Price] Loaded 200,000 rows in 1:54 minutes. Stopped at that point
3rd (Column and then Row) dProduct[Retail Price]{List.PositionOf(dProduct[Product],[Retail Product])} Loaded 200,000 rows in 2:44 minutes. Stopped at that point
For Approximate Match here are the results:
1st "let
Source = Excel.CurrentWorkbook(){[Name=""fSales""]}[Content],
#""Changed Type"" = Table.TransformColumnTypes(Source,{{""Date"", type date}, {""Product"", type text}, {""Units"", Int64.Type}}),
GeertBufferedDiscount = Table.Buffer(dDiscount),
GeertIniDiscount = GeertBufferedDiscount[Discount]{0},
GeertLENdDiscount = Table.RowCount(GeertBufferedDiscount)-1,
GeertAddDiscountFromBufferedDiscount = Table.AddColumn(#""Changed Type"", ""Discount"", each List.Accumulate({0..GeertLENdDiscount},GeertIniDiscount,(Discount,index) => if [Units] >= GeertBufferedDiscount[Units]{index} then GeertBufferedDiscount[Discount]{index} else Discount))
in
GeertAddDiscountFromBufferedDiscount" 04:32 for all 8 million rows
2nd List.Last(Table.SelectRows(dDiscount, (IT) => IT[Quantity]
Hi Mike MR ExcelIsFun, its me Radoslaw Poprawski ( Ichanges my YT account name already)
Im trying to gather up a courage for my first YT Excel and PQ video, but to be honest I'm struggling with WhatIfs about my voice, about what of I forget something and wont notice it, and what if ill suck in general,
if I may Ask, how do you deal with doubts like that or similar ones?
I do have knowledge and I know what I want to present, but I'm scared of whatifs.
@@ExcelInstructor Editing. That is the answer to your question. If you forget or get stuck, just press stop, and then film again : ) I just subscribed to your channel. You will do great : )
@@excelisfun Thank you. I truly hope ill manage that fear of mine.
Hi Mike, I thought that the record.field formula was going to be faster than the merge, but according to your test its not...
Good to know that
@@djcabrera , Glad to help, Eduardo!!
Mike, I finally understand how the custom function works! That little bit you added explaining "=>" took me over the top. Excellent teaching from Grandmaster Mike!
Yes!!!! I love to hear that. Just a syntax thing that Microsoft gave us to confuse and make it seem like it is a comparative operator lol
Thanks for sharing Mike! You explained in a very clear way and I learnt another new trick as always!
simply magic!!!
Glad you like the magic!!!!! M Code Magic!!!!
Thank you for not only this explanation, but your step-by-step break down! Love your teaching style!
Glad it helps!!!
Thanks
Wow!!! Thanks. You are the first ever to use this feature. I appreciate the donation because it helps me to keep making fun videos for the world. In this way we are a Team. Thanks for being an awesome Teammate, Jose Pedro Muniz Vargas!!!!
That's Amazing Mike .
Glad it is amazing for you, Hussein!!!
Great vedio 👍
Glad it is great for you, Excel Expert ; )
Thanks for the Video and most importantly thanks for sharing the working file.
You are welcome for both!!!
Mind blowing
Glad you liked it!!!
Thank you so much Mike, I learned Excel just by watching your videos. Truly Excel is fun when you teach.
I am glad that you like my teaching and can learn, Ravi!!!
It's true. Excel Is fun! Thank you for your videos.
You are so welcome for the fun with Excel, kobi1974!!!!
Hoo, boy! Nothing like a good M coding and coffee in the early morning! ☕ Thanks, Mike!
Yes!!!!! Mike Sugar and M Code with my coffee, please!!!!!!
You are welcome, Hachi!!!
Very detailed. Got to take my time to digest. You are the champ!!!
Time is always available when the videos are posted here at RUclips and left up forever : )
Thanks!
Thank you very much for your support, Hendrick!!! It helps me to keep making videos : )
Wow, that was impressive Mike!
Glad you liked it, Chris : ) : ) : )
Hi Mike. Awesome video.. as always! Lots to study here and many good comments to read below. Thanks for always pushing the limits and showing multiple solutions and hosting/posting discussions and comments that move us all forward every day. Thumbs up!! Go Team :))
Go Forward Every Day Team!!!!!!! Thanks for the new phrase, Wayne ; )
Thanks Mike. That was fantastic. I can't wait for you to get the new functions in Excel and we'll see some videos on that. : )
Yes, I am so pissed at Microsoft. It is total BS that they randomly send out new things. If anyone should get array formula previews, it should be someone like me who consistently writes books, blogs and videdos about array formulas. At least from a marketing point of view, they should keep me in the loop. Darn!!
@@excelisfun So True. In actual fact, they are the ones who are holding things back for people like me to learn more!!!
YOU are the Best Excel teacher ! Great demo. For most lookups, I will stick with merge queries.
Glad you like my vids! Merges are good : )
I have watched Phil's video but yours has more details that make an easy understanding. Thank you Master for sharing your knowledge.
I watched his video and was confused before I watched it a few times. Teaching is a high art and when you leave out important details or present details in a less than advantageous order, things are more difficult to understand. The goal of teaching is to tell storied to make complicated things less complicated.
Thanks Mike for the tutoril and your further research and test on its efficiency in large data set as compared with the usual join operation. You are perfectionist!👍👍👍👍
Glad to help : )
This is great!!! The approximate match 1st solution is exactly what I was looking for. Your explanation makes it easy to understand and apply.
Glad the video helps!!!
When it comes to Power Query i see clearly that Im a beginner while watching those types of videos. Great content. Thanks for sharing! 🙌🙌🙌
In that way we are all beginners with so much to learn. Isn't it awesome : ) : ) : ) : )
Boom!All Great Formulas But Have To Say Really Like The LET Expression Technique...Thank You Mike :)
let expression is pretty slick : ) : ) Plus for us worksheet formula people, it is easy to remember now, because of LET worksheet function!!!!
Fantastic....although not exactly user intuitive....but hey....that's why we have ExcelIsFun and MyOnlineTrainingHub. Thanks Mike, Mynda and Phil...
Go Team!!!!! I Learned the cool formula Column and then Row let formula from them : )
@3:48 Typo in the blue box - Macth for Match (not that it particularly matters)
; )
Thanks Mike ! I preferred the second formula for the exact lookup, and the first one for the approximate lookup :)
So your preferences are:
Exact: Record.Field(dProduct{[Product=[Product]]},"Price")
Approximate: dDiscount[Discount]{List.Count(let U = [Units] in List.Select(dDiscount[Units], each _
@@excelisfun sorry I meant the opposite. Exact with List.PositionOf and the approximate with the variable (IT) which is not so easy to assimilate will watch again this part
1st comment
You get the big trophy today!!! 1st place : )
Let function is more intuitive. And for exact match the technique that resembles index match seems more natural. Mind blowing techniques and video.
let does seem less conceptually difficult than custom function. let seems like a VAR variable in DAX. But the weird thing is that I tend to use the Record.Field most often. Where resembles INDEX for you? is it Record.Field and Custom function one?
@@excelisfun records.field seems more natural and intuitive. May be bcz my brain is used to doing things the excel functions way.
@@abdulhaseeb8027 Got it!!!! I think me too : )
In Korea...Good~~~~
Glad it is good for you, Evanlog : )
Very good, of the 4 types, for Exact Match, I prefer the Looking up the Column then the row and for Approx match, I prefer the Row lookup first, then the column. (Im still trying to learn this stuff and the let expression has been daunting for me.)
Cool! I just times the formulas on over 8 million rows and the Row and then the Column seem to be the fastest in both case (Exact and Approximate).
For Exact match Row first & then column, For Approx match Majority will go for Let function including me I guess. Super video.
Thanks, SIMFINSO!!!
Hi!
Thanks for an awesome video, since this is all new to me, i am still not sure how to preform what i have to do since the problem is a bit more complex.
i need a lookup that will allow me to pull customer assignments based on account number, start month and end month. So basically a lookup with 3 criteria, one "=" , and two with ">=" or "
Still too advanced for me, but I'm getting better at using the built in functions like Merge Queries
M Code is so different than worksheet formulas and DAX - so it does take a long time to learn.
2nd Comment. Greate Video as always. Hats Off to your support for more than a decade. I hope, you would make a video shortly on Partial Match through Power Query.
You get the 2nd place trophy, Kartick!!!!! You are welcome for the support.
Thanks for posting this, helps a lot. Is there a way round a small issue I'm having? I am looking up a product code on my main data against a query table that holds the same code and shows whether it's Core or Non Core. The Query Table is sorted with Core down to Non Core. There are however, some duplicates of codes, so an item can be in this list as both Core and Non Core (it's the way our company links pricing), and I'm getting an error with the first option you supplied because of this. Is there a way to ignore this error? When I used Vlookup or Xlookup previously it gave the first result in the list and essentially stopped looking after that. Many thanks.
👍🏻
Thanks for the thumbs Up : )
Please make a video about set table behavior in power pivot
Hello Sir, thanks for the wonderful video. Please could you help me on how to deal the error.
Good
Glad it helps!!!!
That is amazing! Thanks Sir! But how we do where condition is "Exact match. If none found, return the next larger item"
I would never have known that [ ] inside the { } is a lookup operator. Is there any documentation for this? I'm a complete novice at using the advanced editor and have only been using PQ for about 2 months so I found the video quite challenging and had to watch it in small segments, however, your step approach was particularly useful. Before I would have used merge but it's always good to know other ways to achieve the same result. Thank you for sharing your knowledge, you've earned a new subscriber!
I have a video on M Code that documents: MSPTDA 9: ruclips.net/video/NS1AvfV9BeI/видео.html
Merge is fine to do though.
The whole class is probably what you need: (free at RUclips): ruclips.net/user/playlist?app=desktop&list=PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1
Thanks Mike, again a wonderful lesson. I wonder what method is the most efficient on big data ? column first or row first ? (even if try to fix it usually directly at the source)
As I posted in a few comments below, it is not very efficient to use the formulas on big data. I tried formulas on about 8 million rows and they are horrifically slow. Best to use Merge, or maybe Bill Szysz approximate match UI method.
@@excelisfun Thank you Mike for the answer :) Sorry I did not check out all comments before. I am not so surprised by the terrible performance on big data. If I can not fix the column at the source for any reason I would rather try to add the dDiscount and the fact table in the data model of the Excel file, link them with a one to many relationship and create a calculated DAX column in the fact table, it usually gives better performance on big data ;)
@@Anthony_Lecoq Wil out a doubt, when you have big data, the Data Model and DAX formulas are made specifically for that. That is the way to go.
Thanks Mike, great video. Presumably, for the exact match, record then field, you could return more than one field from the lookup table?
No, neither return duplicates. That is a good question, and I should have covered it in the video, although I did point out that we were dealing with a classic lookup situation where there is a primary key in the lookup table, but:
1) This formula delivers an error when there is a duplicate in the primary key field: Record.Field(dProduct{[Product=[Product]]},"Price")
2) This formula does not deliver an error and only delivers the first occurrence of duplicates when there is a duplicate in the primary key field: dProduct[Price]{List.PositionOf(dProduct[Product],[Product])}
I guess if you wanted dups and then want to expand, you could use a merge or a Table.SelectRows or List.Select.
@@excelisfun Sorry Mike, what I meant was could you return two fields from the same table record. For example Price1 and Price2?
@@davecope3322 Your original question was perfectly worded - I just misinterpreted it lol
Yes, you can return more than one field.
Use this formula: dProduct{[Product=[Product]]} to return a record.
Then use the expand option at the top of the field.
Darn it, I just tried to be a smart a$$ and typed: Table.AddColumn(#"Changed Type", "Price", each dProduct{[Product=[Product]]}[[Price],[Product]])
It works, but it still returns a column of records (but then in reverse order), and you still have to expand it.
Might just as well leave out the column selectors: Table.AddColumn(#"Changed Type", "Price", each dProduct{[Product=[Product]]})
and then expand anyway. :-)
Short answer: sure, whatever you want (du-dum, du-dum...). Just try it out for fun and break the Status Quo. ;-)
Mike, thanks for your dedication!
I also watched Mynda's video. Is this exact lookup version better than using Merge Tables?
You are welcome, Cary!!
I posted this below:
I have worked with up to about 10 million rows with these two Exact Match lookup formulas. Both Exact Match formulas are horrifically slow. Impossibly slow. You would never do it this way. A merge would be MUCH faster. I use both formulas on smaller data sets, but have not detected any performance differences between the two. That is one reason I wanted to ask what others have experienced. For small data sets, I am not sure.
09:37 is the moment, right?
@@excelisfun No, that's the approximate match, if I'm not mistaken.
Did you see my pinned comment, with the performance test results I did for all the formulas and merge? I timed all of them on 8 million rows to see which performed the best.
@@excelisfun Ahh! Looks like Merge and Bill Syzes Table.Join were very fast for exact. You're Record.Field formula for exact was excellent as well. Goes well with your solution from your data analysis video from two years ago. I'm still trying to wrap my head around the approximate match, but I think your custom formula is the easiest to code. Especially for my relatively smaller data.
what about approximate matching with multiple criteria? How do we handle the sorting portion?
I like row then column. I do have a question. Are these case sensitive?
All of Power Query M Code is case sensitive.
Mike again superb video. Question for the exact match would be for me where is the performance better. Have you had any experience with this. Currently my models are build to use table.combine, but this seems to be another better? way. What are your thoughts on this? Thanks
Yes, I connect to an SQL database. I have worked with up to about 8 million rows with these two Exact Match lookup formulas. Both Exact Match formulas are horrifically slow. Impossibly slow. You would never do it this way. A merge would be MUCH faster. I use both formulas on smaller data sets, but have not detected any performance differences between the two. That is one reason I wanted to ask what other have experienced.
You use Table.Combine for lookup?
I just tried the two Approximate Match formulas on an SQL 8 millions row table and it was terribly slow. Both of them. I tried:
Rows and then Column and Custom Function: List.Last(Table.SelectRows(dDiscount, (IT) => IT[Units]
I use it to enhance the original data with information to get the required result. One could discuss if it is the proper way. The task is to transform SAP data from one format into a report with other accounts, and one value for key values (account, trading partner, movement type etc.) for a different reporting database. It cannot be done in SAP for various reasons.
@@excelisfun no sry it is probably merge my bads :)😀
Sir, I have one query regarding conditional formatting with Filter. I want the report in such a way that each customers' sales data should come in a sequence & should separate into 2 different colors. The customers received the goods from multiple plants & I want to filter the customers' sales by plant & it's sales data should come in a sequence & also be separated by highlighting the colors dynamically as we defined in conditional formatting. If I couldn't make you understand my query pls let me know how to share my excel sheet with you for your kind perusal. I couldn't found its solution on youtube so I need your kind support here
how to automatically copy excel values not formulas? thx before
Dear Mike, thanks. Very interesting. And it helps better understand PQ. Would it be safer to use List.Max instead of List.Last in the following formula: "... "Discount1", each List.Last(Table.SelectRows(dDiscount, (IT) => IT[Units]
I think not - just because an approximate match lookup first column must be sorted and then discounts might not necessarily be the largest value in the last row. But as always, when creating solutions, the logic of your particular situation will have the real answer to your max/last question ; )
Can´t open the file, any help?
Key macth misspelling
This method is crazy slow 😪😭