using a dynamic column name in Table.AddColumn columnGenerator

Anonymous — Wed, 09 Mar 2022 10:56:40 GMT

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.

Re: using a dynamic column name in Table.AddColumn columnGenerator

Anonymous — Fri, 11 Mar 2022 07:16:43 GMT

Hi Anonymous ,

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.

Re: using a dynamic column name in Table.AddColumn columnGenerator

Anonymous — Fri, 11 Mar 2022 08:27:08 GMT

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

Anyway, thank you for taking time to answer.

Re: using a dynamic column name in Table.AddColumn columnGenerator

Vijay_A_Verma — Fri, 11 Mar 2022 08:43:41 GMT

Replace your output line with this

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

Re: using a dynamic column name in Table.AddColumn columnGenerator

Anonymous — Fri, 11 Mar 2022 09:06:22 GMT

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.

topic using a dynamic column name in Table.AddColumn columnGenerator in Power Query

using a dynamic column name in Table.AddColumn columnGenerator

Re: using a dynamic column name in Table.AddColumn columnGenerator

Re: using a dynamic column name in Table.AddColumn columnGenerator

Re: using a dynamic column name in Table.AddColumn columnGenerator

Re: using a dynamic column name in Table.AddColumn columnGenerator