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.

9 thoughts on “Multiple Replacements of words in Power Query”

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

  2. 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. 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

  4. //Here’s a version that uses your Table.ReplaceValue idea with List.Accumulate
    //It works against your test data and should perform similarly to your recursive solution (tested on PQ 2.58)

    (InputTable as table, InputColumnName as text,
    ReplacementTable as table, optional StartRow as number) as table =>
    let
    Offset = if StartRow = null then 0 else StartRow,
    Column = each List.Range(Table.Column(ReplacementTable, _), Offset),
    ReplacementPairs = List.Zip({Column(“Old”), Column(“New”)}),

    Replace = (Input as table, FromTo as list) as table =>
    Table.ReplaceValue(Input, FromTo{0}, FromTo{1},
    Replacer.ReplaceText, {InputColumnName})
    in
    List.Accumulate(ReplacementPairs, InputTable, Replace)

Leave a Reply