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

★ Want to automate Excel? Check out our training academy ★

★ Check out the blog post★

★ About this video ★
A few weeks ago, I posted about using Excel's REDUCE and SUBSTITUTE functions to perform multiple find and replace actions to create dynamic text.

But what if we want multiple find and replace during the data-cleaning process? In that scenario, we need to use Power Query to replace values based on a list. So, in this post, let's find out how to do that.

CONTENTS
0:00 Introduction
0:46 Replace values based on a list
6:59 Conclusion

Link to post by Rick De Groot:

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[kebincui]

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 👍👍🌹🌹

[serdip]

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.

[ziggle]

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

[Bashir_Kano]

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.

[IvanCortinas_ES]

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

[BicycleFriends]

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

[z.]

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

[gaybarile]

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

[leebecker]

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

[TheJC_DK]

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 😊👍

[mirrrvelll]

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

[brianh]

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!

[robmedinaXXI]

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.

[GrainneDuggan_Excel]

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

[Bhavik_Khatri]

Very nicely explained List.Accumulate Function.

[josephblack]

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

[williamarthur]

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.

[evadtgov]

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?

[Softwaretrain]

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"}))

[g.oliveira]

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