PQ - multiple replacement using List.Generate()

[sandy666]

SourceChange table

TextWord To ReplaceReplace With

the cat sat on the matcatbear

the cat sat next to the dogmatchair

the dog chased the catdogdragon

the dog sat on the mattheTHE

the catamaran sails through the airairwater


Result:

List.Generate:

Changed Text List.Generate

THE bear sat on THE chwater

THE bear sat next to THE dragon

THE dragon chased THE bear

THE dragon sat on THE chwater

THE bearamaran sails through THE water



// ListGenerate
let
//Get table of word replacements
Replacements = Excel.CurrentWorkbook(){[Name="Replacements"]}[Content],

//Get table containing text to change
TextToChange = Excel.CurrentWorkbook(){[Name="Text"]}[Content],

//Get list of words to replace
WordsToReplace = List.Buffer(Replacements[Word To Replace]),

//Get list of words to replace them with
WordsToReplaceWith = List.Buffer(Replacements[Replace With]),

//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(TextToChange, "Changed Text List.Generate", each ReplacementFunction([Text])),
RC = Table.RemoveColumns(Output,{"Text"})
in
RC