Power Query: How to mass replace values based on a list | Excel Off The Grid 

Alternate solution that may be easier to understand: = Table.ReplaceValue( #"Source table", each [Column to replace], each FindReplace{[Find=[Column to replace]]}[Replace], Replacer.ReplaceValue, {"Column to replace"} )

This is another excellent video from Mark! I feel List.Accumulate function is the most tricky one in M code and one of the few functions with looping effect. Usually one has to study its structure and working mechanism for quite some time before fully understanding it. Mark explains this very clearly and to the point in one go using this great example. Mark skillfully used the M code generated from a designed step as ramp and used it as the key part of the accumulate function element. This not only makes the reader to understand the structure of the List.Accumulate function easily but also paves they way for better understanding of the target this function to achieve in this case. The way Marks explains is both elegant and to the right point for good understanding of this function and its application in this case. Thanks Mark for sharing your wisdom always in the Power Query community 👍👍🌹🌹

Great use of List.Accumulate. Alternatively we can use this formula: = List.Accumulate(Table.ToRecords(FindReplace), #"Changed Type", (state, current) => Table.ReplaceValue(state,current[Find],current[Replace],Replacer.ReplaceText,{"Name"}))

I will use this technique all the time. Thanks Mark.

Thanks for another brilliant video on Power Query techniques for data cleansing and transformation. It's extremely useful and very clearly explained. Thank you so much.

Wow, this video is a game-changer! 🌟 After spending what felt like ages searching for a solution to this incredibly frustrating problem, your method came as a breath of fresh air. The time savings it offers are truly phenomenal. I can't thank you enough for sharing this gem. You've just earned a loyal subscriber! 🙌 #TimeSaver #LifeSaver TLDR: The fundamental idea of the video is to use Power Query's "replace values" action and the accumulate function to quickly and efficiently clean up data by mass-replacing values based on a list. 1. 00:00 🔍 Clean up data by mass-replacing values based on a list of different ways to spell the same company name. 2. 01:17 💡 Use Power Query to easily replace values in a table by creating a list of items to find and their corresponding replacements and then using the "replace values" action in Power Query with the appropriate syntax. 3. 02:39 💡 The speaker explains how to use the list dot accumulate function in Power Query to replace values based on a list. 4. 03:22 📝 Create a list of numbers representing each item in a given list by starting from zero and counting up to the number of items in the list minus one. 5. 04:15 💡 The speaker explains how to use the seed and accumulator function to loop through a list and change values in a table. 6. 05:13 🔁 Each time we loop, the state will update based on the previous result. 7. 05:54 🔍 Use the "find" and "replace" columns in a table to mass-replace values in a dataset, resulting in clean data. 8. 06:57 💡 Replace multiple values in Power Query based on a list using the accumulate function for powerful looping functionality.

The magic has returned. Thanks Mark. Excellent and complete explanation.

I always check your videos, either by clicking on YT notification or redirecting from my mailbox, I find your videos very enriching but majority of the times, the magics look like rocket science to me. Thanks mate.

Many thanks for an excellent video, this was just what I needed to solve the problem I was working on 😊One small improvement would be to make it an exact match, i.e. so if changing from "Wicks" to "Wicks Ltd", and then re-running the query at a future date, it would ignore the lines already correctly named as "Wicks Ltd" rather than making them "Wicks Ltd Ltd". This can be achieved by changing the "ReplaceText" to "ReplaceValue" at the end of the List.Accumulate function. Thanks again, and I have subscribed to the channel, as there are some really good tips and tricks videos, especially for Power Query 😊👍

One of the clearest explanations of the Accumulate function I have seen. Thanks Mark.

This is extremely useful to clean up bad data from multi sources! Bravo!

This was exactly what I needed and presented in a short, concise, yet complete manner. Issues I found (being a complete noob at Power Query) was getting the data into Power Query and that the data is case sensitive (I obviously figured both of those out). Well done, well done indeed!

Super clear and easy to follow. Thank you so much.

This is almost perfect 😃 Instead of matching the full Find string, I'd like to match a partial string, aka Text.Contains. Adding this to the function generates an error "cannot convert a value of type Function to type Text." Ideas?

This is a great tutorial! Thank you so much. This is exactly what I am looking for.

Thanks Mark! This is exactly what I was looking for :)

Excellent tutorial.

Very nicely explained List.Accumulate Function.

Great video! I have a small problem that's causing an issue though - to simplify, if I have a column of data that contains the word "house" and I want to change this to "home", and I also have the word "warehouse" in the same column, I end up with an entry that's corrected to "home", but I also end up with "warehome" - is there a way to make sure the case and cell contents are matched and exact?

This issue bothered me for ages, lot of replacement steps for nothing...but this is amazing. Thanks!!

Great Tutorial.. but from practical perspective.. do you see any performance improvement on large data vs using merge technique to fetch the correct name by lookup to replacement table.

thank you i used it 8 times in my query replacing Merges and Change types so nice and clean now!

Thanks. It helped me alot.

Well that was a surprise when that worked, I'd been trying for ages but using column headers as the list to accumulate as for transform, really ingenious. Now to replace them conditionally.

You can use if else or best switch statement

Hi. Since only the list of values for the [Find] column was referenced in the beginning, how does the current moved to the [Replaced] column when declared later down the line?

Gostaria de agradecer por ter compartilhado. Irá me ajudar muito. Parabéns!

Great! Very helpful, thanks Mark

Sweet Mark! Have you tested the performance of this on large data sets? Just wondering if it's slower or faster than a bunch of "find-n-replace" steps...

Hello, your Power Query videos helped me with important things. Could you help me? How to create multiply columns in the table using the Advanced Editor?. I created a function and it is called many times, which ends up thinking a little. Each column has its own name. And if you create the dynamic columns, based on a list of names, is it possible?

👏 amazing

Excellent Mark

Excellent

Excellent, easy to follow video, fantastic. Would be good to know if this is possible to be saved as a custom function, if you have multiple columns from multiple tables in your model that need replacing from the FindReplace table then it be useful to be able to just quickly call on a custom function to do this.

The solution presented in the video is certainly robust and elegant. I thought, however, that I could achieve the same results by using an outer left join merge query on the Data and FindReplace tables on [Name] = [Find]. Then I added a custom column ([Standardized Name]) with a formula like = if [Replace] = null then [Name] else [Replace]. Then I kept just the [Standardized Name] and [Total] columns and finally renamed [Standardized Name] to just [Name]. Would this approach be as efficient as your M-Code solution? Are there advantages or issues with one approach over the other? Thank you kindly.

Just as a follow up I found that if replacing over multiple columns some of which did not contain text i got errors to used a custom replacer, i stared by make virtual old /new table ; let t = #table( {"old", "new"}, { {"sheep", "Goat"} , { "teddy", "paddington"} ,{ "mice", "rats"}} ) in List.Accumulate( {0..2} , Source, (S,C)=> Table.ReplaceValue( S, t [old] {C}, t[new] {C}, (x,y,z)=> if Value.Type(x) = type text then Replacer.ReplaceText( x, y, z ) else x , Table.ColumnNames(Source) ) )

May i ask for your help i have a data with Me1 Me2 should be replace with ME_yes But the output is shows as "ME_yes_yes" instead of "ME_yes"

Thank you.

And you can just add to that table of findreplace to continue to find?

What an excellent video!!. Thank you for the teaching. Is there a way to consider null values as a "Find" criteria in order to replace it with a non-null value?

HELP....this doesn't match the entire cell contents, only partials, so I am getting really jumbled up results

Thanks for the awesome video

How to change the texts in multiple columns

Excellent video and instructions. How can I download the Excel practice file? I have a similar task to find and replace on 7800+ rows. thanks.

Nice, another good example is "How to Merge Excel Files with Different Headers in Power Query | List.Accumulate" by Leila Gharani here on RU-vid. Supper confusing at first! 🙃🤨😕

List.zip would work too here, right?

Hey Mark! I tried this on my dataset and received a stack overflow error :(

there's a bug in this method . If I have the word MURALE in Find to become MURALI in the Replace column, and then I have MURALETTO to become MURALETTI, it will change this last word in MURALITTI (with an "I" instead of an "E") because it doesn't search for the exact word, but it identifies the word MURALE inside of the word MURALETTO and will change it to MURALI even insede the word MURALETTO that becomes incorrectly MURALITTI.

For beginners its 100% over the head 😂😂😂