Source |
|
Change table |
|
Text |
|
Word To Replace |
Replace With |
the cat sat on the mat |
|
cat |
bear |
the cat sat next to the dog |
|
mat |
chair |
the dog chased the cat |
|
dog |
dragon |
the dog sat on the mat |
|
the |
THE |
the catamaran sails through the air |
|
air |
water |
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 |
Code:
// 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
Bookmarks