replace multiple words in string with specific words from list

Question

How can I, using M-language, replace specific words in a string with other specific words that are specified in a table?

See my example data:

Source code:

let
    someTable = Table.FromColumns({{"aa &bb &cc dd","&ee ff &gg hh &ii"}, {Table.FromColumns({{"&bb","&cc"}, {"ReplacementForbb", "ccReplacement"}},{"StringToFind", "ReplaceWith"}), Table.FromColumns({{"&ee", "&gg","&ii"}, {"OtherReplacementForee", "SomeReplacementForgg", "Replacingii"}},{"StringToFind", "ReplaceWith"})}, {"aa ReplacementForbb ccReplacement dd","OtherReplacementForee ff SomeReplacementForgg hh Replacingii"}},{"OriginalString", "Replacements", "WantedResult"})
in
    someTable

Answer 1

This is a neat question. You can do this with some table and list M functions as a custom column like this:

= Text.Combine(
      List.ReplaceMatchingItems(
          Text.Split([OriginalString], " "),
          List.Transform(Table.ToList([Replacements]),
              each Text.Split(_,",")
          )
      ),
  " ")

I'll walk through how this works using the first row as an example.

---

The [OriginalString] is "aa &bb &cc dd" and we use Text.Split to convert it to a list.

"aa &bb &cc dd" --Text.Split--> {"aa", "&bb", "&cc", "dd"}

Now we need to work on the [Replacements] table and convert it into a list of lists. It starts out:

StringToFind  ReplaceWith
------------------------------
&bb           ReplacementForbb
&bb           ccReplacement

Using Table.ToList this becomes a two element list (since the table had two rows).

{"&bb,ReplacementForbb","&cc,ccReplacement"}

Using Text.Split on the comma, we can transform each element into a list to get

{{"&bb","ReplacementForbb"},{"&cc","ccReplacement"}}

which is the form we need for the List.ReplaceMatchingItems function.

List.ReplaceMatchingItems(
    {"aa", "&bb", "&cc", "dd"},
    {{"&bb","ReplacementForbb"},{"&cc","ccReplacement"}}
)

This does the replacement and returns the list

{"aa","ReplacementForbb","ccReplacement","dd"}

Finally, we use Text.Combine to concatenate the list above into a single string.

"aa ReplacementForbb ccReplacement dd"