Multiple Replacement Entire Cell Content Matching Specific Text

[absherzad]

Hi everyone,

I want to customize Ivan Bondarenko's custom 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?
• To keep the performance in mind, it is worth it to mention that I want to apply this function on 4 separate columns, each includes duplicate instances. The idea crossed my mind is to create 4 reference from original query and then perform the remove duplicates transformation on each of the referenced query individually. Finally use the merge transformation feature to join them back together. What do you think of this approach?

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

Thank you,