Thanks Wyn, an excellent video that's very well explained and stepped through. And thanks for being honest that you didn't just bang those last 2 solutions out 🙂. Power Query is incredible but the syntax can be pretty tricky and with 700+ functions it's about knowing the art of the possible and lots of practice. Bookmarked this one for when excellent performance on large datasets is essential.
Hi Wyn, Generally I like the single line of code solutions I tried to use it before in a complex PowerQuery and my refresh time went to the roof. Therefor I stepped back to the Query Merge options. Is is easy to understand and very fast. Thanks for putting the different options side by side.
Hey Wyn, Here’s my experience on approx. match lookup. If you want to do exact or approximate match in PQ-M, yes List.Accumulate (LA) is efficient. Then again Table.Combine is beautiful function as well (that’s a hint). OTOH, I very much like to use the List.PositionOf (LPOSO) function for both, that is: including approximate match lookup. (Really, you can) Excelisfun has a video on approximate lookup to which I reacted giving various methods (including List.Generate per Mike’s request). In my experience LA and especially LPOSO are the fastest. I believe those methods to be a little less convoluted that your methods in this video, honestly. BTW: indeed most important: tables (or lists!) that are called over and over in a function should be buffered right before usage for max. performance.
I kew I had used this or similar method so had a look through and found this ; List.Accumulate( Table.ColumnNames( Source ) , Source, (s,c)=> Table.TransformColumns( s, { c, each Text.Combine( List.ReplaceMatchingItems( Splitter.SplitTextByWhitespace() (_), OldNew )," ") }) ) , The To.Columns is very fast even though it requries nested list transform if text is to be split, I did try working on records but there was not speed difference, but i just like records.
Brilliant as always. This video helps us to understand clearly how those functions works in steps. Thanks Wyn ❤👍
Thank you
You are great in explaining, intellectually honest and generous. Thank you!
Very kind of you
Very helpful explanations!! Thank you for showing the results of all the different options. Would love to see more videos like this!
Thanks. I appreciate you taking the time to let me know you found it useful
Hi Wyn, excellent video, very well edited! I really appreciate the facts that you are citing all your references.
Thank you for the insightful comment.
Thanks Wyn, an excellent video that's very well explained and stepped through. And thanks for being honest that you didn't just bang those last 2 solutions out 🙂. Power Query is incredible but the syntax can be pretty tricky and with 700+ functions it's about knowing the art of the possible and lots of practice. Bookmarked this one for when excellent performance on large datasets is essential.
You’re welcome. Thanks for the feedback
@@AccessAnalytic ...and one quick one: how did set up the small table/350k table switcheroo?
I used a parameter( so pull the number into Power Query ) then use it in a Keep Top Rows step
Always love a power query deep dive, thanks for the this.
You’re welcome. I appreciate you taking the time to let me know you found it useful
Hi Wyn,
Generally I like the single line of code solutions I tried to use it before in a complex PowerQuery and my refresh time went to the roof. Therefor I stepped back to the Query Merge options. Is is easy to understand and very fast.
Thanks for putting the different options side by side.
You’re welcome.
Well done. Love it.
Great explanations.
Thank you
Brilliant !!! Very Good👋👋
Thank you
Hey Wyn,
Here’s my experience on approx. match lookup.
If you want to do exact or approximate match in PQ-M, yes List.Accumulate (LA) is efficient.
Then again Table.Combine is beautiful function as well (that’s a hint).
OTOH, I very much like to use the List.PositionOf (LPOSO) function for both, that is: including approximate match lookup.
(Really, you can)
Excelisfun has a video on approximate lookup to which I reacted giving various methods (including List.Generate per Mike’s request).
In my experience LA and especially LPOSO are the fastest. I believe those methods to be a little less convoluted that your methods in this video, honestly.
BTW: indeed most important: tables (or lists!) that are called over and over in a function should be buffered right before usage for max. performance.
Do you have a blog post or video that demonstrates LPOSO?
Just amazing. thank you!
You’re welcome. Thanks for leaving a kind comment
I kew I had used this or
similar method so had a look through and found this ;
List.Accumulate( Table.ColumnNames( Source ) , Source, (s,c)=>
Table.TransformColumns( s,
{ c, each Text.Combine( List.ReplaceMatchingItems(
Splitter.SplitTextByWhitespace() (_), OldNew )," ") }) ) , The To.Columns is very fast even though it requries nested list transform if text is to be split, I did try working on records but there was not speed difference, but i just like records.
Are you able to re-phrase that code so it works with my example