The Fastest Replace Values method using a list in Power Query

[@kebincui]

Brilliant as always. This video helps us to understand clearly how those functions works in steps. Thanks Wyn ❤👍

[@AccessAnalytic]

Thank you

[@VideoAndrega]

You are great in explaining, intellectually honest and generous. Thank you!

[@AccessAnalytic]

Very kind of you

[@bryanvenn5721]

Very helpful explanations!! Thank you for showing the results of all the different options. Would love to see more videos like this!

[@AccessAnalytic]

Thanks. I appreciate you taking the time to let me know you found it useful

[@jeromeastier462]

Hi Wyn, excellent video, very well edited! I really appreciate the facts that you are citing all your references.

[@AccessAnalytic]

Thank you for the insightful comment.

[@w13ken]

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.

[@AccessAnalytic]

You’re welcome. Thanks for the feedback

[@w13ken]

@@AccessAnalytic ...and one quick one: how did set up the small table/350k table switcheroo?

[@AccessAnalytic]

I used a parameter( so pull the number into Power Query ) then use it in a Keep Top Rows step

[@Back1Ply]

Always love a power query deep dive, thanks for the this.

[@AccessAnalytic]

You’re welcome. I appreciate you taking the time to let me know you found it useful

[@StephanBenne]

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.

[@AccessAnalytic]

You’re welcome.

[@emujkic]

Well done. Love it. Great explanations.

[@AccessAnalytic]

Thank you

[@mehulthakkar6094]

Brilliant !!! Very Good👋👋

[@AccessAnalytic]

Thank you

[@GeertDelmulle]

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.

[@AccessAnalytic]

Do you have a blog post or video that demonstrates LPOSO?

[@patrickharilantoraherinjat2994]

Just amazing. thank you!

[@AccessAnalytic]

You’re welcome. Thanks for leaving a kind comment

[@williamarthur4801]

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.

[@AccessAnalytic]

Are you able to re-phrase that code so it works with my example