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
In addition, it performs replacement on thousands of rows in appropriate time.
You can download the sample workbook here.
Nice work!
Thanks, Chris!
And huge thanks for your blog! I couldn’t do many things without your examples.
Thank you, Ivan. May I use your function in my M script? (of course, with reference to your blog)?
Hi Alexey, sure, feel free to use and share it.
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?
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
//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)