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

let
ReplaceAll = (InputTable as table, ColumnName as text, ReplacementTable as table, optional StartRow as number) =>
let
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
else
@ReplaceAll(result, ColumnName, ReplacementTable, NextRow)
in
OutputTable
in
ReplaceAll

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.

Advertisements

6 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)?

  2. absherzad 2017-07-20 / 16:29

    Thank you for the nice work,
    I want to customize your function to replace the entire cell content containing specified text, for instance, using the Text.Contains() function.
    It however, seems the provided replacer functions supported by the Table.ReplaceValue() function are Text.Replace() or Value.Replace().
    Do you think it is a good idea and efficient to create a new conditional column using the Text.Contains() function in your custom function and how?

  3. absherzad 2017-07-20 / 17:05

    I tried with the following customization but did not work out:

    let
    ReplaceAll = (InputTable as table, ColumnName as text, ReplacementTable as table, optional StartRow as number) =>
    let
    ActualRow = if (StartRow =null) then 0 else StartRow,
    //result = Table.ReplaceValue(InputTable, ReplacementTable{ActualRow}[Original Text], ReplacementTable{ActualRow}[New Text] ,Replacer.ReplaceText, {ColumnName}),
    result = Table.AddColumn(InputTable, “Custom”, if Text.Contains({ColumnName}, ReplacementTable{ActualRow}[Original Text]) then ReplacementTable{ActualRow}[New Text] else null ),

    NextRow = ActualRow + 1,

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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