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 : )
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!
@@excelisfun For how my brain is wired, the first options work better! I may try the second later down the road :) Funny how some of these videos never age. Always great to go back to. Thanks again Mike!
Mike, quick follow up, have you encountered issues with these specific formulas when loading the query to the Data Model? I am getting extremely slow loads (and eventually crashes) for data set of over 1.2M rows. When loaded as a regular connection or as a pivot table, no issues.
@@YvesAustin There is an alternative formula using List.Accumulate. I have it in my up and coming M Code book that should be out in a few weeks. Here is the code: //SalesDiscountAcc (Sales as number) as number => let // Hard coded DiscountTable = sales/discount lookup table DiscountTable = #table(type table [Sales = number, Discount = number], {{0,0},{500,0.025},{1000,0.045},{2500,0.075}}), // Rows in table minus one becasue M Code is base zero Rows = Table.RowCount(DiscountTable)-1 in List.Accumulate( {0..Rows}, null, // cs = Current state of discount selection // cr = Current row in list iteration (cs, cr) => if DiscountTable{cr}[Sales]
Other ideas: 1) If the increment between the values in the first column of the approximate match lookup table are equal, you can create a CEILING function helper column in the fact table in the Data Model using the DAX CEILING function, and this would be to create a column that you can then use for a relationship. 2) You could also do this hack: append discount table and then some other tricks and totally avoids a lookup table in each cell in the column (which is the cause of the slowdown): let Source = Excel.CurrentWorkbook(){[Name="fSalesAprox"]}[Content], AddDataTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", type number}}), AppendDiscountTable = Table.Combine({AddDataTypes, disDiscount01}), SortToGetSalesDicountLowerLimitToTop = Table.Sort(AppendDiscountTable,{{"Sales", Order.Ascending}}), FilledDownDiscounts = Table.FillDown(SortToGetSalesDicountLowerLimitToTop,{"Discount"}), FilterOutNulls = Table.SelectRows(FilledDownDiscounts, each ([Date] = #date(2023, 1, 20))), SortInvoice = Table.Sort(FilterOutNulls,{{"Invoice", Order.Ascending}}) in SortInvoice 3) In M Code, you could also create a key column in fact table (M Code does not have CEILING), then do a merge: let Source = Excel.CurrentWorkbook(){[Name="fSalesAprox"]}[Content], AddDataTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", type number}}), LookupDiscountKey = Table.AddColumn(AddDataTypes, "SalesDiscountKey", each List.Min({2000,Number.RoundDown([Sales]/500,0)*500})), MergeToLookupDiscount = Table.NestedJoin(LookupDiscountKey, {"SalesDiscountKey"}, disDiscount02, {"Sales"}, "disDiscount02", JoinKind.LeftOuter), ExpandGetDiscount = Table.ExpandTableColumn(MergeToLookupDiscount, "disDiscount02", {"Discount"}, {"Discount"}), RemoveKeyColumn = Table.RemoveColumns(ExpandGetDiscount,{"SalesDiscountKey"}) in RemoveKeyColumn
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!!!!
Thank you, Mike, You saved my day! I was able to successfully apply the Exact Match Lookup: Column and then Record. This was easier than the Record and then Column. I was unable to apply any of the DAX functions and was searching for a suitable alternative when I found your video!😀 None of the courses I took or the books I referred to had any useful reference on how to use Custom Columns,
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?
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!!
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.
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
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 :))
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!👍👍👍👍
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.)
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 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.
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.
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.
@@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. ;-)
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 ;)
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.
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.
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
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 "
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.
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 ; )
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
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
I am going to need to reference this video more than once! Saved in my favs! Awesome!
Yes, at first we all do, but then we all get it : )
@@excelisfun For how my brain is wired, the first options work better! I may try the second later down the road :) Funny how some of these videos never age. Always great to go back to. Thanks again Mike!
Mike, quick follow up, have you encountered issues with these specific formulas when loading the query to the Data Model? I am getting extremely slow loads (and eventually crashes) for data set of over 1.2M rows. When loaded as a regular connection or as a pivot table, no issues.
@@YvesAustin There is an alternative formula using List.Accumulate. I have it in my up and coming M Code book that should be out in a few weeks. Here is the code:
//SalesDiscountAcc
(Sales as number) as number =>
let
// Hard coded DiscountTable = sales/discount lookup table
DiscountTable =
#table(type table [Sales = number, Discount = number],
{{0,0},{500,0.025},{1000,0.045},{2500,0.075}}),
// Rows in table minus one becasue M Code is base zero
Rows = Table.RowCount(DiscountTable)-1
in
List.Accumulate(
{0..Rows},
null,
// cs = Current state of discount selection
// cr = Current row in list iteration
(cs, cr) =>
if
DiscountTable{cr}[Sales]
Other ideas:
1) If the increment between the values in the first column of the approximate match lookup table are equal, you can create a CEILING function helper column in the fact table in the Data Model using the DAX CEILING function, and this would be to create a column that you can then use for a relationship.
2) You could also do this hack: append discount table and then some other tricks and totally avoids a lookup table in each cell in the column (which is the cause of the slowdown):
let
Source = Excel.CurrentWorkbook(){[Name="fSalesAprox"]}[Content],
AddDataTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", type number}}),
AppendDiscountTable = Table.Combine({AddDataTypes, disDiscount01}),
SortToGetSalesDicountLowerLimitToTop = Table.Sort(AppendDiscountTable,{{"Sales", Order.Ascending}}),
FilledDownDiscounts = Table.FillDown(SortToGetSalesDicountLowerLimitToTop,{"Discount"}),
FilterOutNulls = Table.SelectRows(FilledDownDiscounts, each ([Date] = #date(2023, 1, 20))),
SortInvoice = Table.Sort(FilterOutNulls,{{"Invoice", Order.Ascending}})
in
SortInvoice
3) In M Code, you could also create a key column in fact table (M Code does not have CEILING), then do a merge:
let
Source = Excel.CurrentWorkbook(){[Name="fSalesAprox"]}[Content],
AddDataTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", type number}}),
LookupDiscountKey = Table.AddColumn(AddDataTypes, "SalesDiscountKey", each List.Min({2000,Number.RoundDown([Sales]/500,0)*500})),
MergeToLookupDiscount = Table.NestedJoin(LookupDiscountKey, {"SalesDiscountKey"}, disDiscount02, {"Sales"}, "disDiscount02", JoinKind.LeftOuter),
ExpandGetDiscount = Table.ExpandTableColumn(MergeToLookupDiscount, "disDiscount02", {"Discount"}, {"Discount"}),
RemoveKeyColumn = Table.RemoveColumns(ExpandGetDiscount,{"SalesDiscountKey"})
in
RemoveKeyColumn
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!!!!
Thank you, Mike, You saved my day! I was able to successfully apply the Exact Match Lookup: Column and then Record. This was easier than the Record and then Column. I was unable to apply any of the DAX functions and was searching for a suitable alternative when I found your video!😀 None of the courses I took or the books I referred to had any useful reference on how to use Custom Columns,
I am so glad to help : ) : ) : ) : )
Thank you for not only this explanation, but your step-by-step break down! Love your teaching style!
Glad it helps!!!
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!!!
Thanks for the Video and most importantly thanks for sharing the working file.
You are welcome for both!!!
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!!!
Thanks!
Thank you very much for your support, Hendrick!!! It helps me to keep making videos : )
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!!!
Thanks for sharing Mike! You explained in a very clear way and I learnt another new trick as always!
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 : )
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 : )
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 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!!!
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.
Wow, that was impressive Mike!
Glad you liked it, Chris : ) : ) : )
That's Amazing Mike .
Glad it is amazing for you, Hussein!!!
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 : ) : ) : ) : )
It's true. Excel Is fun! Thank you for your videos.
You are so welcome for the fun with Excel, kobi1974!!!!
@3:48 Typo in the blue box - Macth for Match (not that it particularly matters)
; )
simply magic!!!
Glad you like the magic!!!!! M Code Magic!!!!
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
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 ; )
Great vedio 👍
Glad it is great for you, Excel Expert ; )
For Exact match Row first & then column, For Approx match Majority will go for Let function including me I guess. Super video.
Thanks, SIMFINSO!!!
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 : )
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!!!!
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).
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 : )
Mind blowing
Glad you liked it!!!
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.
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.
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. ;-)
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.
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 :)😀
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.
1st comment
You get the big trophy today!!! 1st place : )
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
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 "
In Korea...Good~~~~
Glad it is good for you, Evanlog : )
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.
Please make a video about set table behavior in power pivot
I like row then column. I do have a question. Are these case sensitive?
All of Power Query M Code is case sensitive.
That is amazing! Thanks Sir! But how we do where condition is "Exact match. If none found, return the next larger item"
Hello Sir, thanks for the wonderful video. Please could you help me on how to deal the error.
what about approximate matching with multiple criteria? How do we handle the sorting portion?
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 ; )
how to automatically copy excel values not formulas? thx before
Good
Glad it helps!!!!
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
👍🏻
Thanks for the thumbs Up : )
Can´t open the file, any help?
Key macth misspelling
This method is crazy slow 😪😭