Multiple Replacements of words in Power Query

Post of Chris Webb inspired me on creation of alternative way to make replacement in table column using separate table with list of Old & New values. Say, more scalable solution. Because List.Generate, used by Chris, and cell by cell replacement is very slow method.
Another Chris’s post helped me with it. The idea is to use recursion, go line by line of Replacement table and apply Table.ReplaceValue function.

After playing around with PQ I came to following function

ReplaceAll = (InputTable as table, ColumnName as text, ReplacementTable as table, optional StartRow as number) =>
ActualRow = if (StartRow =null) then 0 else StartRow,
result = Table.ReplaceValue(InputTable, ReplacementTable{ActualRow}[Old], ReplacementTable{ActualRow}[New] ,Replacer.ReplaceText, {ColumnName}),

NextRow = ActualRow + 1,

OutputTable = if NextRow > (Table.RowCount(ReplacementTable)-1)
then result
@ReplaceAll(result, ColumnName, ReplacementTable, NextRow)

Using this function I get same result
PQ Replacement

In addition, it performs replacement on thousands of rows in appropriate time.
You can download the sample workbook here.


4 thoughts on “Multiple Replacements of words in Power Query

    • Ivan Bondarenko 2015-04-16 / 15:27

      Thanks, Chris!
      And huge thanks for your blog! I couldn’t do many things without your examples.

  1. Alexey 2017-05-22 / 15:53

    Thank you, Ivan. May I use your function in my M script? (of course, with reference to your blog)?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s