Search

# Multiple Find Replace with Power Query List.Accumulate()

Share

Imagine you have a paragraph of text and you want to replace all occurrences of {four, normal, mysterious, nonsense} with {six, casual, confounding, handbags}. How would you do that?

You could use SUBSTITUTE() formula, but you need to nest four of them (as we need to replace four values with another four). But what if you have larger set of find / replacements?

Worry not, you can use Power Query to transform original text to new one by replacing all matching values.

In this page, learn how to do that with the excellentÂ List.Accumulate()Â Power Query function.

## The set up for multiple find / replace

Let’s start with two tables –Â mytextÂ with the original text values andÂ replacementsÂ with rules for replacement.

I have included a snapshot of these tables below.

## Power Query transformations

Start by loading both of these tables to Power Query. We will transformÂ mytext table to add a column with replaced text.

Before we could do the transformations, you must learn 3 key concepts.

They are,

1. List.Accumulate() Function
2. Getting Lists from Table columns
3. How to access individual list items
If you know these three concepts, skip ahead to next section. Else, read on.
Key concepts for multiple find replace

List.Accumulate() function allows us to perform same action on items on a list and return a combined value.

A simple example for this is,

Imagine you have a list of 10 numbers {1..10}

You want to calculate the total of these numbers.

You can use List.Accumulate() to add them up.Â Of course, you can also use List.Sum() function, but this is a demo of List.Accumulate() dear.

Assuming the original list is inÂ source, we can use List.Accumulate() to add up items like this:

=List.Accumulate(Source, 0, (state, current) => state+current)

The output of this step will be 55, sum of list with first 10 numbers.

ðŸ¤¯ðŸ¤¯ðŸ¤¯

What’s going on here?

List.Accumulate() is aÂ cumulative function. It takes a list and a starting value (in our case these areÂ sourceÂ andÂ 0) and applies a function on each of the list items while modifying theÂ initial value.

Let’s see what that means for our list.

• For each item in the list
• Update state to state + current (ie 0+1 initially, then 1+2, 3+3, 6+4…45+10)
• At the end of the list, it returns the final state value,Â ieÂ 55.

How to write the third argument of List.Accumulate()?

The third argument of List.Accumulate() is a function with two parameters – state, current.Â

• State:Â this will be initially set to 2nd parameter of the function and changes every time List.Accumulate() moves down the list.
• Current: this will the value of current list item

Power Query functions are written in this fashion.

(parameter1, parameter2…) => function definition

So, for example (state, current) => state+current is a function that takes 2 parameters and returns their sum.

If you have a table namedÂ replacementsÂ with columns [Find] and [Replace] in Power Query, you can use below syntax to extract a table column as list.

replacements[Find]

tablename[Column name]

If you have a list named source in Power Query with 10 items, you can access 5th item of the list with this syntax.

source{4}

So, for example, if you want to get 3rd item of the [Find] column in replacements table, use:

=replacements[Find]{2}

Note: Power Query uses 0 base for lists. So first item of the list will be list{0}

## Using List.Accumulate for mass Find / Replace

Now that you are familiar with key concepts necessary, let’s do some replacements.

Go toÂ mytextÂ query and insert a custom column. In this column, we will generated replaced text.

Write this formula:

```List.Accumulate(
List.Numbers(0, Table.RowCount(replacements)),
[Text],
(state, current) =>
Text.Replace(state,
replacements[Find]{current},
replacements[Replace]{current}))
```

How does this formula work?

1. We create a list of numbers from 0. The size of this list will be same as number of rows in replacements table.Â
1. For our sample data, we get {0,1,2,3}
3. We replace first replacements[Find] value with replacements[Replace] in [Text]
4. We repeat 3 step three, while updating theÂ stateÂ
5. At the end of this process, we end up with [Text] that successivelyÂ replaced all words in replacements table.

I know this formula is tricky to get your head around, but once you understand it, you will see the potential for other cool applications.

## List.Accumulate explained - Video

If you are still ðŸ˜• and not sure how List.Accumulate magic works, then please watch this video. It goes in to greater detail about thisÂ beautiful M function. See it below or visit my YouTube channel for it.

## More on Power Query...

List.Accumulate examples:

Power Query examples for data cleanup:

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### Automatically Format Numbers in Thousands, Millions, Billions in Excel [2 Techniques]

Ever wanted to automatically format values in thousands, millions or billions in Excel? In this article, let me show you two powerful techniques to do just that.

## Related Tips

Chandoo.org Podcast Sessions

Learn Excel

Power BI

Learn Excel

Learn Excel

Power Query

### 10 Responses to “Multiple Find Replace with Power Query List.Accumulate()”

1. Ola says:

Note: The text-formula above miss a -1. The video is correct.

2. Ola says:

ðŸ˜€ sorry, I made the exact same mistake as you did - initially - in the video. {0..3} is one thing, and Table.RowCount(replacements) -->3 items ={0,1,2} is another thing.

3. yassine says:

1st question : you've created a new column to put the replacements in. how ca we replace in the original column without creating a new one ?

2nd question : how can we replace the value in the entire cell and not only the text (Using ReplaceValue instead of text.Replace) ?

• Vassilis says:

Chandoo, I would be very interested to have your answers in both Yassine's questions!

Thank you for sharing,
Vassilis

4. Noah says:

Thank you for this! I was just doing an assignment where I was having to replace words with other words in my Excel sheet. We are starting with the basics in my class, so I know I don't have a huge list that I would need to find and replace, but this is something that could be useful down the road for me!

5. Nick Ryder says:

Hi

This is nearly perfect for my needs thank you, however I would like just the "replace" to be the result if possible please, I have tried in vain adjusting the formula without success.

6. Huisie says:

Thank you! Awesome tip, and very flexible, too.

My find/replace values were in non-adjacent columns of a table containing a bunch of other data. Worked prefectly and I am now a tiny bit less clueless.

7. Renata says:

Hi!!!

I have the same question that Yassine did.

I need to replace the values in the same column. I don't want to create a new column e then have to remove the old column.
How could I could that?

8. That's a real fun article. It inspired me to delve deeper into the topic of List.Accumulate. I can see how the function works, but it takes an additional step to imagine how one can use it for more complex applications.

Your example also made it into my article, together with some other use-cases. Would be great to get your opinion on which other areas you would include in the article.

Let me drop the link to the page so others can too find it for further reading: https://gorilla.bi/power-query/list-accumulate/

Expression.Error: The name 'replacements' wasn't recognized. Make sure it's spelled correctly.

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.Â