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