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.
