using a dynamic column name in Table.AddColumn columnGenerator

[prorokrak]

Hi, all,

I would like to build on a very useful query Chriss Webb published on his blog (Using List.Generate() To Make Multiple Replacements Of Words In Text In Power Query). I would like to turn it into a function so that I could easily reuse it in different projects.

This will be a signature of the function:

(TableToChange as table, ColumnToMakeReplacements as text, ReplacementsTable as table, optional SearchForColumnName as text, optional ReplaceWithColumnName as text) as table =>

However, I cannot figure out how I can provide a variable to a columnGenerator in the last step called Output. See this function-like query:

let
    // Create a table with text to be replaced
    Source = Json.Document("[{""Text"":""the cat sat on the mat""},{""Text"":""the cat sat next to the dog""},{""Text"":""the dog chased the cat""},{""Text"":""the dog sat on the mat""},{""Text"":""the catamaran sails through the air""}]"),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Text"}, null, ExtraValues.Error),
    #"Expanded Source Text" = Table.ExpandRecordColumn(#"Converted to Table", "Text", {"Text"}, {"Text"}),

    // Create a table of replacement pairs
    Source2 = Json.Document("[{""Word To Replace"":""cat"",""Replace With"":""bear""},{""Word To Replace"":""mat"",""Replace With"":""chair""},{""Word To Replace"":""dog"",""Replace With"":""dragon""},{""Word To Replace"":""the"",""Replace With"":""THE""},{""Word To Replace"":""air"",""Replace With"":""water""}]"),
    #"Converted to Table2" = Table.FromList(Source2, Splitter.SplitByNothing(), {"ReplacementsTable"}, null, ExtraValues.Error),
    #"Expanded ReplacementsTable" = Table.ExpandRecordColumn(#"Converted to Table2", "ReplacementsTable", {"Word To Replace", "Replace With"}, {"Word To Replace", "Replace With"}),

    // Initialize variables
    TableToChange = #"Expanded Source Text", 
    ColumnToMakeReplacements = "Text",
    ReplacementsTable = #"Expanded ReplacementsTable",
    SearchForColumnName = "Word To Replace",
    ReplaceWithColumnName = null,

    //Get table of word replacements
    Replacements = ReplacementsTable,
    //Get list of words to replace
    WordsToReplace = 
        if SearchForColumnName = null then 
            List.Buffer(Replacements[Search For])
        else 
            List.Buffer(Table.Column(Replacements, SearchForColumnName)),
    //Get list of words to replace them with
    WordsToReplaceWith = 
        if ReplaceWithColumnName = null then 
            List.Buffer(Replacements[Replace With])
        else 
            List.Buffer(Table.Column(Replacements, ReplaceWithColumnName)),
    //A non-recursive function to do the replacements
    ReplacementFunction = (InputText)=> 
     let
       //Use List.Generate() to do the replacements
       DoReplacement = List.Generate(
                          ()=> [Counter=0, MyText=InputText], 
                          each [Counter]<=List.Count(WordsToReplaceWith), 
                          each [Counter=[Counter]+1, 
                                MyText=Text.Replace(
                                         [MyText], 
                                         WordsToReplace{[Counter]}, 
                                         WordsToReplaceWith{[Counter]})], 
                          each [MyText]),
       //Return the last item in the list that
       //List.Generate() returns
       GetLastValue = List.Last(DoReplacement)
     in
      GetLastValue,
    //Add a calculated column to call the function on every row in the table
    //containing the text to change
    Output = Table.AddColumn(TableToChange, ColumnToMakeReplacements & " Replaced Text", each ReplacementFunction(Table.Column(TableToChange, ColumnToMakeReplacements)))
in
    Output

The last step works if the column name is hard-coded:

Output = Table.AddColumn(TableToChange, ColumnToMakeReplacements & " Replaced Text", each ReplacementFunction([Text]))

I know that with this syntax, the columnGenerator always passes just one row (or one value) on each iteration of DoReplacement function. It seems that when I provide the column using Table.Column function, it passes the whole column instead of a row.

I do not know how should I adjust it to make of the column I provide to the columnGenerator dynamic. I am sure that it is a no-brainer for experienced Power Query users, but I got stuck on this. Appreciate your help.

Edit: I realized that even though the Chris Webb's blog was one of the sources used in this solution, I have actually modified a solution from @ImkeF that can be found here: Multiple replacements or translations in Power BI and Power Query. Sorry for attributing incomplete credits.

[Vijay_A_Verma · Accepted solution]

Replace your output line with this

Output = Table.AddColumn(TableToChange, ColumnToMakeReplacements & " Replaced Text", each ReplacementFunction(Record.Field(_,ColumnToMakeReplacements)))

[prorokrak]

Hi @Vijay_A_Verma, that was it. I hoped that some slight adjustment should do the trick and here we are! It works like a charm. Thanks a lot for your help.

[v-stephen-msft]

Hi @prorokrak ,

I think it seems a bit difficult, because the ReplacementFunction provided in the blog also refer to the columns in the data source. So it's a bit difficult to say dynamic.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[prorokrak]

Okay, so it is not so straightforward as I originally thought. Sad to hear that.

Anyway, thank you for taking time to answer.