Multiple Find Replace with Power Query List.Accumulate()

August 02, 2020 Power Query,

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,

List.Accumulate() Function
Getting Lists from Table columns
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

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.

We start with 0 (call this starting state)
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.

To learn more about List.Accumulate():

Watch the video that accompanies this article. You will get clarity about List.Accumulate()

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?

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}
We start with input [Text] column value
We replace first replacements[Find] value with replacements[Replace] in [Text]
We repeat 3 step three, while updating the state
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.

Download Multiple Find Replace Example file

Please click here to download the multiple find replace example file

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.