Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

Power Query: How to multiple replace values based on a list

Power Query Replace Values Based On List

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.

Download the example file: Click the link below to download the example file used for this post:

Watch the video

Watch the video on YouTube

Example data

The scenario we are working with is as follows:

  • Each division of your company has submitted a list of sales by customer
  • You have combined the data into a single table for analysis
  • However, each division is not consistent in their customer naming
  • You need to find and replace the customer names to get a clean data set

Here is the data we are working with. This has been loaded into Power Query in a query called Data.

Power Query List - Customer Names

In this data set, the following values are the same customer:

  • Magic Marvin | Marvin Hilton
  • Sarah Toote | Sarah Toote (t/a Epic Designs)
  • Whittlesborough Lodge Limited | Whittlesborough Lodge Ltd
  • Wicks Limited | Wick’s Limited | Wicks Ltd

Therefore, this is the Table of values to find and replace; it has been loaded into Power Query as a query called FindReplace.

Find and replace List

Perform a single replace values transformation

Let’s start by getting the syntax to replace a single value.

In the Data query, select the Name column, then click Transform > Replace Values

Transform - Replace Values in Power Query

Enter any values in the Replace Values dialog box; we don’t care; we just want the syntax. I’ve used Find This and Replace With. Then click OK.

Replace values in PQ

The formula bar at the top displays the M code created.

= Table.ReplaceValue(#"Changed Type","Find This",
"Replace With",Replacer.ReplaceText,{"Name"})

The key parts are:

  • #”Changed Type”: The name of the table to perform the transformation on
  • “Find This”: The text to find
  • “Replace With”: The text to replace
  • {“Name”}: The list of columns to perform the replace values action on

Copy this formula from the formula bar; we will need it later.

Then delete the step.

Using List.Accumulate to find and replace values based on a list

OK, now comes the tricky part.

We are going to use the List.Accumulate function in Power Query. This does not appear anywhere in the User Interface, so we have to write the code by hand.

List.Accumulate loops through a list and performs a transformation for each item in that list.

To learn more about List.Accumulate, check out this post:

Click on the fx icon next to the formula bar and enter the following:

= List.Accumulate(
{0..List.Count(FindReplace[Find]) - 1},
#"Changed Type",
(state, current) => state

The key parts are:

  • List.Accumulate: The name of the function which perform the looping activity.
  • {0..List.Count(FindReplace[Find]) – 1}: Creates a list of numbers from 0 to the count of items in the Find column of the FindReplace query. Because Power Query is zero-based, we need to minus 1. This creates the list to loop through.
  • #”Changed Type”: The start value for the initial state. In our example, this is the name of the previous step.
  • (state, current) => state: The function to perform for each item in the list.
    • state: the intermediate result after the transformation on each item
    • current: the value currently used inside the loop.

At present, this function achieves nothing; there is nothing happening inside the loop; it’s simply returning the existing state with each loop.

Now it’s time to use the code we copied earlier and add it into the loop (see the bold section below)

= List.Accumulate(
{0..List.Count(FindReplace[Find]) - 1},
#"Changed Type",
(state, current) => Table.ReplaceValue(#"Changed Type",
"Find This","Replace With",

Before this works, we need to make a few changes:

  • Change #”Changed Type” to state
    This ensures we use the state after the previous loop
  • Change “Find This” to FindReplace[Find]{current}
    This returns the current value from the Find column of the FindReplace query
  • Change “Replace With” to FindReplace[Replace]{current}
    This returns the current value from the Replace column of the FindReplace query

The query now looks like this

= List.Accumulate(
{0..List.Count(FindReplace[Find]) - 1},
#"Changed Type",
(state, current) => Table.ReplaceValue(state,

That’s it. Take a look at the Preview Window. All the changes have been made.

Result after multiple find replace

BOOM! Now we can analyze this data.


Replace values in Power Query is applied one value at a time. Therefore, it is difficult to use if there are lots of values, or an unknown number of values. However, finding and replacing values based on a list is possible by leveraging the power of List.Accumulate.

Related posts:

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

One thought on “Power Query: How to multiple replace values based on a list

  1. Rick de Groot says:

    Mark – nice one. Works very effectively on Case-senstive replacements too.

    A suggestion: I would probably swap around the first argument of List.Accumulate and make a direct reference to the translation table.

    You could either use
    – Table.ToRows
    – Table.ToRecords ( )

    I’d probably go for the Table.ToRecords one. Simply because it allows you to reference the field name of the items to swap.


Leave a Reply

Your email address will not be published. Required fields are marked *