OK, first off: back in the day when Mike was exploring Approximate Match Lookup in Power Query, I came up with this List.Accumulate solution ("LA" for short). So, for those who want to hate or criticize this solution, you can put the blame on me. Truth be told: I didn't time it, that was Mike's doing. I was just "into List.Accumulate (...)" (just like other(s) were into recursion) - it was a bit of a fixation. Little did I know that it performs that well, compared to other solutions. BTW: for big data, avoid having to sort - that is way too slow a proces. But what about Table.Select: surely it is optimized and all? Well, as it turns out: LA is faster. Now let it be clear: List.Accumulate does a fixed number of iterations - it iterates over every item in the entire list in its first argument. It doesn't bail out of the iteration beforehand. If you want that kind of behavior you need to turn to List.Generate (LG for short). Will the latter be performing better then? Well, IME (in my experience): no. In LG you have to do some overhead that is not needed in LA: keep track of the position counter, you iteratively generate an entire (structured) List, and you need to extract the part that you need from LG's output. The good news: it's still faster than Table.Select, though. Yay! 🙂 But wait, I have one more thing: What if no (explicit) iteration is needed for this? Enter my brand new List.PositionOf solution! (LPOSO) Ever so slightly faster than my LA-solution. Mike, I sent you my update to your (large) file (now it's even larger :-) containing: an optimized LA-solution (yes, I made it even (slightly) better), an optimized (?) LG-solution (new!) and my brand new LPOSO-solution. The latter is the fastest and the simplest (so, forget about that sort-solution - Sorry, Oz! ;-). Finally, there's a catch: in order to understand my best (to date) LPOSO-solution, you need to read some unique section of Mike's book - the section on Comparer functions. It's all so very easy (once you know it), isn't it? 🙂 Mike, maybe a little update of your book is in order? (LPOSO) - LOL! The work is never done, it just keeps evolving...
Here's the M-Code for those that want to try it out for size: • LA - List.Accumulate: let Source = Excel.CurrentWorkbook(){[Name="SalesBig"]}[Content], AddDataTypes = Table.TransformColumnTypes(Source,{{"Sales", type number}, {"Date", type date}}), DiscountSales = List.Buffer(disDiscount01[Sales]), DiscountPc = List.Buffer(disDiscount01[Discount]), DisSize = List.Count(DiscountPc)-1, AddDiscount = Table.AddColumn(AddDataTypes, "Discount", each List.Accumulate({0..DisSize},0,(cs,cr) => if DiscountSales{cr} [pos=0,dis=0], each Sales >= DiscountSales{[pos]}, each [pos=[pos]+1,dis=DiscountPc{[pos]+1}]))[dis]) in AddDiscount • LPOSO - List.PositionOf: let Source = Excel.CurrentWorkbook(){[Name="SalesBig"]}[Content], AddDataTypes = Table.TransformColumnTypes(Source,{{"Sales", type number}, {"Date", type date}}), DiscountSales = List.Buffer(disDiscount01[Sales]), DiscountPc = List.Buffer(disDiscount01[Discount]), AddDiscount = Table.AddColumn(AddDataTypes, "Discount", each DiscountPc{List.PositionOf(DiscountSales,[Sales],null,(a,b) => b
I would be very greatful if you give me a solution to count meals separately Breakfast, lunch, diner from one date to another for example Check in date 05-09-2024 LUNCH & Check out date 09-09-2024 DINER Formula should count meals like a person had 4nos breakfast (e.g. 6B,7B,8B,9B), 4nos Lunches & 5nos Diners Only meals count required
I have seen many other video tutorials that talk about List.Accumulate. I must confess that it is not really a frequently used function for my solutions. But after watching Mike's video, I think I should reconsider adopting this feature for performance reasons when working with big data. Thanks Mike, great video!
@@excelisfun I wanted to express my appreciation for your incredible UA-cam content, which has greatly enhanced my Excel skills. I'm eager to continue learning and was wondering if you offer any paid courses on Udemy. I would be thrilled to purchase one, as I believe it would significantly aid my academic pursuits. Thank you for all the knowledge you've shared, and I look forward to your response.
I see a new video by Girvin, I watch that video, and *BA-AM* I understand something new. I've already ordered and received the .pdf of his new book on M code; my study has just gotten to the methods he's describing here. BUY THE BOOK.
List.Accumulate is really awesome. I've used it in a "replacer" table to standardize a list of customers where there are multiple ship-to's using the following: = List.Accumulate( {0..List.Count(ReplacerTable[Existing]) - 1} , #"Removed Columns2", (state,current) => Table.ReplaceValue (state, ReplacerTable [Existing]{current},ReplacerTable[Replace]{current},Replacer.ReplaceText, {"Customer name"}) ). Took me a little experimenting but I got it to work! Thanks Mike for another great video !
Just used this trick few days ago. My suggestion is before doing the batch find/replacement, you'd better sort 'Exisitng' column in descending order first.
Mike, your videos have helped me enormously. As a small token of my appreciation, I placed an advanced order for your new book. Best wishes for the future.
Hello Mike, Thanks for posting another amazing video. It served to sharpen the approx match lookup skills in power query you taught in your DAME MPT lectures. In the end, the comparison between the Table.SelectRows function, List.Accumulate and the Append trick timings when we have a million rows of data was very interesting. Append trick can be very cool for someone who is new to power query. Reference the fact table in a new query, Append the dimension table, sort the main key to look up (Sales) in your example in ascending order followed by sorting the Date column in ascending order, then right click the discount column and Fill Down. Finally, filter the Date column and uncheck null.
I tend not to agree. If the beginner has a good source of knowledge, then it should be easier. For a beginner I think that understanding the one line of code for the Table.SelectRows re-usable function in the Advanced Editor is easier to understand than the Append code. The one line is: (Sales as number) as number => List.Last(Table.SelectRows(disDiscountAprox, each [Sales]
Thank you for the amazing videos. This channel is a goldmine for everyone! With 3.7k videos! . I need your guidance on how best to follow your content. could you recommend the most suitable playlist or section for me to focus on?
It depends on what you want to learn. On my excelisfun UA-cam home page, watch the intro video, if describes the different free classes and ways to learn. www.youtube.com/@excelisfun
@@excelisfun Thanks for your reply. I have intermediate-level Excel skills and a basic understanding of Power Query. Now, I want to advance my Power Query knowledge, specifically in M Language and DAX. With so many videos on your channel, I'm unsure where to start. Could you please suggest the exact playlist or provide some guideline videos to help me follow the right path?
You taught me soooooooo much, Geert Delmulle : ) : ) In specific: thank you for teaching me the List.Accumulate and how the if stopped it, not the List.Accumulate! I already had to mention that in another comment from @rick_ Check it out.
@@excelisfun Yes, it can (List.Generate). I remember mentioning that in our 1-2-1 conversation about the topic. List.Generate has the property of variable length looping (While loop). And you can always extract the last element of the List as a post processing of List.Generate (as answer to your question), you know: “curly brackets is for items (List-variant)”. ;-)
@@GeertDelmulle But is the formula faster, as the commented intimated? I tried to create a aprox match formula, but stopped because it creates a list... But List.Last comes to rescue.
I wanted to express my appreciation for your incredible UA-cam content, which has greatly enhanced my Excel skills. I'm eager to continue learning and was wondering if you offer any paid courses on Udemy. I would be thrilled to purchase one, as I believe it would significantly aid my academic pursuits. Thank you for all the knowledge you've shared, and I look forward to your response.
Hi Mike, very thorough as always. Regarding about List.accumulate not evaluating all rows: I belive it will iterate over the entire list as if statement only determines the value retuned in each iteratoration. There is no implicit "exit loop".
@@victor_wang_1 List.Generate creates a list. The goal of the approximate match lookup is to create a single value. How does List.Generate do that? If you know, please post - it would be cool if there was a faster way : ) : ) : )
@@victor_wang_1 That is correct. BTW: the statement “the IF statement ends the loop” is a bit ambiguous: better would be: “the IF statements freezes the solution (but List.Accumulate iterates over the entire dTable)” (dimension Table).
@@excelisfun I also hsd a go at using the same logic for a running total with a criteria ; Table.AddColumn( Start, "N", (A)=> List.Accumulate( {0..Table.RowCount(Start)-1} , {}, (s,c)=> if Start {c} [Date]
Or append the tables (column names on key column needs to be the same), sort it and fill down the looked up value. On larger tables if would be slow, but no need for a complex formula. Btw, it's Is dus Soleil who came up with that.
I think I saw that in Ken Puls/Miguel Escobar book too. Append lookup->sort->fill up/down as needed->delete empty key rows (to delete the appended lookup table). Slower but easy to maintain by a non-pro :)
@@dziadeck47 How is it easier to maintain than the reusable Table.SelectRows functions? Or the List.Accumulate? The formula is one line of code in the advanced editor: (Sales as number) as number => List.Last(Table.SelectRows(disDiscountAprox, each [Sales]
Why can’t Microsoft just make python works with power query instead of developing their own language again? Within excel, now you have excel own native functions, vba, office scripts, power query M language, power pivot DAX and the upcoming python. 😂😂😂😂😂
Whoa! That PowerQuery programming language is way too complicated to use. Until I saw this video, I didn’t know it existed. I already spent 25 years learning VBA, SQL, and Excel’s functions... and now to start all over again with an even more complicated language? Help!
You are right. There are always many languages to learn. We do get to choose : ) In all my books and comprehensive videos classes I always compare and contrast the four major functional Microsoft functional languages to showcase how each is valuable. It took me many years to become fluent in each and then become one of the few bloggers on the earth to compare and contrast and present the situations that each language is useful and most powerful. However, the decline in my channel and then the revoking of my MVP status came after all of that. Perhaps it is a losing cause. When I used to just blogged about Excel, things were good...
@@excelisfun thanks Mike for sharing your experience. I can’t imagine how much work it took to learn all this, in addition to everything else. I am impressed by your knowledge and experience. Microsoft clearly did a disservice in revoking your MVP status. I continue to learn VBA and SQL, and it was my hope to learn PowerQuery. Perhaps I really should get your book. Knowledge is power.
@@dschmid8845 You are welcome and thank you for your support, Teammate!!! SQL is the one that seems like it will always rule and will never go away : )
This is my VLOOKUP Function: Name this function VLOOKUP: (lookupValue as any, Range as table, colIndex as number, approximateMatch as logical) => let // Sort table by first column and buffer sortedRecords = Table.Buffer(Table.Sort(Range,{{Table.ColumnNames(Range){0}, Order.Ascending}})),
L1 = Table.ToColumns(sortedRecords){0}, L2 = Table.ToColumns(sortedRecords){colIndex -1}, match = if approximateMatch = true then
List.Accumulate( {0..List.Count(L1)-1}, L1{0}, (Tstart,index)=> if lookupValue >= L1{index} then L2{index} else Tstart)
else List.Accumulate( {0..List.Count(L1)-1}, "#N/A", (Tstart,index)=> if lookupValue = L1{index} then L2{index} else Tstart) in match
That is so WAY cool. I have not seen someone simulate VLOOKUP in M Code!!! How long have you been using that? I tried it on the big data set in the video, but it calculated longer than the others including the multiple step Append solution. I can't seem to tell why though. When I count the rows of the lookup table, I always did the variable as a let step, not in the functions. That is the only thing I can see that is different. But still does not seem to be a problem. I can't see why the variables or if expression would slow it down either... Maybe my data set is odd. What has your experience been with performance?
OK, first off: back in the day when Mike was exploring Approximate Match Lookup in Power Query, I came up with this List.Accumulate solution ("LA" for short).
So, for those who want to hate or criticize this solution, you can put the blame on me.
Truth be told: I didn't time it, that was Mike's doing. I was just "into List.Accumulate (...)" (just like other(s) were into recursion) - it was a bit of a fixation.
Little did I know that it performs that well, compared to other solutions.
BTW: for big data, avoid having to sort - that is way too slow a proces.
But what about Table.Select: surely it is optimized and all? Well, as it turns out: LA is faster.
Now let it be clear: List.Accumulate does a fixed number of iterations - it iterates over every item in the entire list in its first argument. It doesn't bail out of the iteration beforehand. If you want that kind of behavior you need to turn to List.Generate (LG for short).
Will the latter be performing better then? Well, IME (in my experience): no.
In LG you have to do some overhead that is not needed in LA: keep track of the position counter, you iteratively generate an entire (structured) List, and you need to extract the part that you need from LG's output. The good news: it's still faster than Table.Select, though. Yay! 🙂
But wait, I have one more thing: What if no (explicit) iteration is needed for this?
Enter my brand new List.PositionOf solution! (LPOSO) Ever so slightly faster than my LA-solution.
Mike, I sent you my update to your (large) file (now it's even larger :-) containing: an optimized LA-solution (yes, I made it even (slightly) better), an optimized (?) LG-solution (new!) and my brand new LPOSO-solution. The latter is the fastest and the simplest (so, forget about that sort-solution - Sorry, Oz! ;-).
Finally, there's a catch: in order to understand my best (to date) LPOSO-solution, you need to read some unique section of Mike's book - the section on Comparer functions. It's all so very easy (once you know it), isn't it? 🙂
Mike, maybe a little update of your book is in order? (LPOSO) - LOL! The work is never done, it just keeps evolving...
Here's the M-Code for those that want to try it out for size:
• LA - List.Accumulate:
let
Source = Excel.CurrentWorkbook(){[Name="SalesBig"]}[Content],
AddDataTypes = Table.TransformColumnTypes(Source,{{"Sales", type number}, {"Date", type date}}),
DiscountSales = List.Buffer(disDiscount01[Sales]),
DiscountPc = List.Buffer(disDiscount01[Discount]),
DisSize = List.Count(DiscountPc)-1,
AddDiscount = Table.AddColumn(AddDataTypes, "Discount", each List.Accumulate({0..DisSize},0,(cs,cr) => if DiscountSales{cr} [pos=0,dis=0], each Sales >= DiscountSales{[pos]}, each [pos=[pos]+1,dis=DiscountPc{[pos]+1}]))[dis])
in
AddDiscount
• LPOSO - List.PositionOf:
let
Source = Excel.CurrentWorkbook(){[Name="SalesBig"]}[Content],
AddDataTypes = Table.TransformColumnTypes(Source,{{"Sales", type number}, {"Date", type date}}),
DiscountSales = List.Buffer(disDiscount01[Sales]),
DiscountPc = List.Buffer(disDiscount01[Discount]),
AddDiscount = Table.AddColumn(AddDataTypes, "Discount", each DiscountPc{List.PositionOf(DiscountSales,[Sales],null,(a,b) => b
I would be very greatful if you give me a solution to count meals separately Breakfast, lunch, diner from one date to another
for example
Check in date
05-09-2024 LUNCH &
Check out date 09-09-2024 DINER
Formula should count meals like a person had 4nos breakfast (e.g. 6B,7B,8B,9B), 4nos Lunches & 5nos Diners
Only meals count required
I have seen many other video tutorials that talk about List.Accumulate. I must confess that it is not really a frequently used function for my solutions. But after watching Mike's video, I think I should reconsider adopting this feature for performance reasons when working with big data. Thanks Mike, great video!
Glad to help!!!!
@@excelisfun I wanted to express my appreciation for your incredible UA-cam content, which has greatly enhanced my Excel skills. I'm eager to continue learning and was wondering if you offer any paid courses on Udemy. I would be thrilled to purchase one, as I believe it would significantly aid my academic pursuits.
Thank you for all the knowledge you've shared, and I look forward to your response.
I see a new video by Girvin, I watch that video, and *BA-AM* I understand something new.
I've already ordered and received the .pdf of his new book on M code; my study has just gotten to the methods he's describing here.
BUY THE BOOK.
Thank you, thank you, thank you for the kind words about my book : ) : ) : ) : )
Thank you Mike for an awesome video. I have little knowledge of M Code. I am learning from your videos and book.
M Code is like the ghost behind the scenes to deal with data: ghostly dwd : ) : ) : )
@@excelisfun M Ghost 👻
Thanks Mike. That was GREAT!!!! :) :)
Glad that you liked it, Formula Guy John!!!
List.Accumulate is really awesome. I've used it in a "replacer" table to standardize a list of customers where there are multiple ship-to's using the following: = List.Accumulate(
{0..List.Count(ReplacerTable[Existing]) - 1} , #"Removed Columns2", (state,current) => Table.ReplaceValue (state, ReplacerTable [Existing]{current},ReplacerTable[Replace]{current},Replacer.ReplaceText, {"Customer name"})
). Took me a little experimenting but I got it to work! Thanks Mike for another great video !
Wow!!! I love that, Chris M : ) Can you send me your example? I want to see your cool invention! excelisfun at gmail.
Sure, I'll send it over MIke
@@chrism9037 Yes!!!!!!!!
Just used this trick few days ago. My suggestion is before doing the batch find/replacement, you'd better sort 'Exisitng' column in descending order first.
Mike, your videos have helped me enormously. As a small token of my appreciation, I placed an advanced order for your new book. Best wishes for the future.
Thank you very much, Kevin!!!! : )
Hello Mike, Thanks for posting another amazing video. It served to sharpen the approx match lookup skills in power query you taught in your DAME MPT lectures. In the end, the comparison between the Table.SelectRows function, List.Accumulate and the Append trick timings when we have a million rows of data was very interesting.
Append trick can be very cool for someone who is new to power query. Reference the fact table in a new query, Append the dimension table, sort the main key to look up (Sales) in your example in ascending order followed by sorting the Date column in ascending order, then right click the discount column and Fill Down. Finally, filter the Date column and uncheck null.
I tend not to agree. If the beginner has a good source of knowledge, then it should be easier. For a beginner I think that understanding the one line of code for the Table.SelectRows re-usable function in the Advanced Editor is easier to understand than the Append code. The one line is:
(Sales as number) as number => List.Last(Table.SelectRows(disDiscountAprox, each [Sales]
@@excelisfun You are the best teacher in Excel. Anyone including beginner can do magic with excel as always with the learnings from your videos :)
Love this, using Sales all over it makes it a little confusing though
Excellent as always, thanks Mike 👍❤
You are welcome!!!!
Wow!! Thanks for sharing this informative video Mike!
You are welcome for the fun wow : ) : )
i am from azerbaijan.i wanna thank you a lot.
You are welcome a lot!!!!
Thank you for the amazing videos. This channel is a goldmine for everyone! With 3.7k videos! . I need your guidance on how best to follow your content. could you recommend the most suitable playlist or section for me to focus on?
It depends on what you want to learn. On my excelisfun UA-cam home page, watch the intro video, if describes the different free classes and ways to learn. www.youtube.com/@excelisfun
Or tell me what you want and I can suggest : )
@@excelisfun Thanks for your reply. I have intermediate-level Excel skills and a basic understanding of Power Query. Now, I want to advance my Power Query knowledge, specifically in M Language and DAX. With so many videos on your channel, I'm unsure where to start. Could you please suggest the exact playlist or provide some guideline videos to help me follow the right path?
@@rezanurkabir70 Yes. For hard core DAX and M Code: my latest playlists: DAME: ua-cam.com/play/PLrRPvpgDmw0lAIQ6DPvSe_hfAraNhTvS4.html
Ah, good stuff. Recognise the topic ;-)
And the quotes from the book, too.
Thanks for putting this into a video. :-)
You taught me soooooooo much, Geert Delmulle : ) : ) In specific: thank you for teaching me the List.Accumulate and how the if stopped it, not the List.Accumulate! I already had to mention that in another comment from @rick_ Check it out.
Have you heard about List.Generate for approximate match lookup? Someone commented below...
I don't see how a function that generates a list could lookup a single value...
@@excelisfun Yes, it can (List.Generate).
I remember mentioning that in our 1-2-1 conversation about the topic.
List.Generate has the property of variable length looping (While loop).
And you can always extract the last element of the List as a post processing of List.Generate (as answer to your question), you know: “curly brackets is for items (List-variant)”. ;-)
@@GeertDelmulle But is the formula faster, as the commented intimated? I tried to create a aprox match formula, but stopped because it creates a list... But List.Last comes to rescue.
didn't know m is that much flexible. thank you sir
It is quite an amazing language - if you learn a bit - it is easy to have fun with : ) : ) : )
Pure Magic :) Thanks a lot!
Glad it is magic for you and Team : ) : ) : )
I learn all about excel n powerquery from your video. Can you please share how to send power query file through mail or share to someone
Let's go next new video ...❤
I wanted to express my appreciation for your incredible UA-cam content, which has greatly enhanced my Excel skills. I'm eager to continue learning and was wondering if you offer any paid courses on Udemy. I would be thrilled to purchase one, as I believe it would significantly aid my academic pursuits.
Thank you for all the knowledge you've shared, and I look forward to your response.
Could you please explore the python feature added in excel. I want some videos on it too. Include it too in the EMT series.
Hi Mike, very thorough as always. Regarding about List.accumulate not evaluating all rows: I belive it will iterate over the entire list as if statement only determines the value retuned in each iteratoration. There is no implicit "exit loop".
That is correct. The if expression stops it, not List.Accumulate. I thought I was very careful not to say that List.Accumulate stops it!?!?
@@excelisfunUsing List.Generate you can exit the loop. I’ve seen better performance with Generate than Accumulate, though it’s harder to use.
@@victor_wang_1 List.Generate creates a list. The goal of the approximate match lookup is to create a single value. How does List.Generate do that? If you know, please post - it would be cool if there was a faster way : ) : ) : )
@@victor_wang_1 O, if faster, we could generate list and use List.Last... I will have to go and try that : )
@@victor_wang_1 That is correct.
BTW: the statement “the IF statement ends the loop” is a bit ambiguous: better would be: “the IF statements freezes the solution (but List.Accumulate iterates over the entire dTable)” (dimension Table).
Really like the accer, I had a go on my own first, I but didn't quite get it, I was not bringing back the state for the else part so only some worked.
That is awesome that you try first!!!! That is always what I do too : ) It is more fun that way.
@@excelisfun I also hsd a go at using the same logic for a running total with a criteria ;
Table.AddColumn( Start, "N", (A)=>
List.Accumulate( {0..Table.RowCount(Start)-1} , {}, (s,c)=>
if Start {c} [Date]
@@williamarthur4801 And especially when you create the re-usable function - the code is just so short and quick to type out : )
Taught fully power bi
I am not sure what you are asking. Can you restate?
@@excelisfun teach full course of power bi
PQ kingdom has a king and M(ike's) code rule✌🏼😉
Thank you, Kind of LAMBDA and Array C!!!!!!
Or append the tables (column names on key column needs to be the same), sort it and fill down the looked up value.
On larger tables if would be slow, but no need for a complex formula. Btw, it's Is dus Soleil who came up with that.
I think I saw that in Ken Puls/Miguel Escobar book too. Append lookup->sort->fill up/down as needed->delete empty key rows (to delete the appended lookup table). Slower but easy to maintain by a non-pro :)
@excel-in-g, Yes, I showed the append method at the end of the video - it takes WAY to long to calculate. You want to avoid that method at all costs.
@@dziadeck47 How is it easier to maintain than the reusable Table.SelectRows functions? Or the List.Accumulate? The formula is one line of code in the advanced editor:
(Sales as number) as number => List.Last(Table.SelectRows(disDiscountAprox, each [Sales]
@excel-in-g, BTW, that is cool to know that dus Soleil invented that. How did you discover that fact?
Hi Mike, it's Oz du Soleil. (Stupid autocorrection on my phone) I think you know him. I follow his channel, that's how.
each in power query is turrrrup in excel (your sound of dragging the formula down)
Thank you for the cool turrrrrrup fun : ) : ) : )
Why can’t Microsoft just make python works with power query instead of developing their own language again? Within excel, now you have excel own native functions, vba, office scripts, power query M language, power pivot DAX and the upcoming python. 😂😂😂😂😂
So agree... I have spent my whole 25 Excel life learning new languages lol or : ( or : )
Whoa! That PowerQuery programming language is way too complicated to use. Until I saw this video, I didn’t know it existed. I already spent 25 years learning VBA, SQL, and Excel’s functions... and now to start all over again with an even more complicated language? Help!
You are right. There are always many languages to learn. We do get to choose : ) In all my books and comprehensive videos classes I always compare and contrast the four major functional Microsoft functional languages to showcase how each is valuable. It took me many years to become fluent in each and then become one of the few bloggers on the earth to compare and contrast and present the situations that each language is useful and most powerful. However, the decline in my channel and then the revoking of my MVP status came after all of that. Perhaps it is a losing cause. When I used to just blogged about Excel, things were good...
@@excelisfun thanks Mike for sharing your experience. I can’t imagine how much work it took to learn all this, in addition to everything else. I am impressed by your knowledge and experience. Microsoft clearly did a disservice in revoking your MVP status. I continue to learn VBA and SQL, and it was my hope to learn PowerQuery. Perhaps I really should get your book. Knowledge is power.
@@dschmid8845 You are welcome and thank you for your support, Teammate!!! SQL is the one that seems like it will always rule and will never go away : )
@@dschmid8845 If you get Mike's book, you won't regret it. You can trust me on that one! ;-)
This is my VLOOKUP Function:
Name this function VLOOKUP:
(lookupValue as any, Range as table, colIndex as number, approximateMatch as logical) =>
let
// Sort table by first column and buffer
sortedRecords = Table.Buffer(Table.Sort(Range,{{Table.ColumnNames(Range){0}, Order.Ascending}})),
L1 = Table.ToColumns(sortedRecords){0},
L2 = Table.ToColumns(sortedRecords){colIndex -1},
match = if approximateMatch = true then
List.Accumulate(
{0..List.Count(L1)-1},
L1{0},
(Tstart,index)=> if lookupValue >= L1{index} then L2{index} else Tstart)
else
List.Accumulate(
{0..List.Count(L1)-1},
"#N/A",
(Tstart,index)=> if lookupValue = L1{index} then L2{index} else Tstart)
in
match
That is so WAY cool. I have not seen someone simulate VLOOKUP in M Code!!! How long have you been using that? I tried it on the big data set in the video, but it calculated longer than the others including the multiple step Append solution. I can't seem to tell why though. When I count the rows of the lookup table, I always did the variable as a let step, not in the functions. That is the only thing I can see that is different. But still does not seem to be a problem. I can't see why the variables or if expression would slow it down either... Maybe my data set is odd. What has your experience been with performance?
@@excelisfun I've only used it for smaller data sets for fun.
@@josh_excel It is very cool. I saved it : ) : )
See other comment about my LPOSO-solution. 🙂