Replace multiple values in the same column in one step

Just my two cents for a one line solution, since I couldn't really find what I was looking for and this thread comes up first. Please be aware that this solution only works when replacing full strings, not part of a string:

#"multiReplace" = Table.TransformColumns
(
  SourceTable,
  {
    {
      "ADR_SEAL",
      (baseString) =>
      Table.FirstValue
      (
        Table.FindText
        (
          #table
          (
            {"new", "old"},
            {
              {"Zwart", {"", null, "Z"}},
              {"Blanco", {"B"}}
            }
          ),
          baseString
        ),
        baseString
      ),
      type text
    }
  }
)

In the example above I:

- define a mappingstable with old values (list of values to be found and replaced) and new values,

- return the lines of the mappingstable where the old value is found

- return the first value of the resulting table (the replacement value)

- return the base string if no values are found to be replaced.

Please find a more elaborate example below:

#"Replaced Value" = Table.TransformColumns
(
  #"Removed Other Columns",
  {
    {
      "ADR_SEAL",
      (baseString) => let trimmedText = Text.Trim(baseString) in Table.FirstValue(Table.FindText(Table.Buffer(#table({"newValue", "oldValues"}, {{"Zwart", {"", null, "Z"}}, {"Blanco", {"B"}}})), trimmedText), trimmedText),
      type text
    },
    {
      "ADR_ACTIEF",
      (baseString) => let trimmedText = Text.Trim(baseString) in Table.FirstValue(Table.FindText(Table.Buffer(#table({"newValue", "oldValues"}, {{true, {"J"}}, {false, {"N", "", null}}})), trimmedText), trimmedText),
      type logical
    },
    {
      "ADR_EORI",
      (baseString) => let trimmedText = Text.Trim(baseString) in Table.FirstValue(Table.FindText(Table.Buffer(#table({"newValue", "oldValues"}, {{null, {""}}})), trimmedText), trimmedText),
      type text
    }
  }
)

In this example I also:

1. replace multiple values at once per column

2. trim the inputvalues (we have an ERP that adds a lot of spaces to empty strings unfortunately)

3. buffer the mapping tables

4. change the type to text or logical

5. replace values of multiple columns at once.

Note that the table I supply contains the new value as a first value per record. This is essential because of Table.FirstValue.

so the table structure is like:

#table(
  {"newValue", listOfOldValues"},
  {
    {"firstReplacementValue", {"Value1ToBeReplaced", "Value2ToBeReplaced"}},
    {"secondReplacementValue", {"Value3ToBeReplaced", null, ""}}
  }
)

("" / null values are possible and are just values to be replaced).

It might be wise to test with / without Table.Buffer, as this might break query folding and I'm not sure about the performancegain (if any).

Hopefully others find this useful too.

Cheers,

Niels

Edit: corrected my reply due to an unfortunate mistake in the formula (forgot to return the base value in case there was nothing to replace). Apologies to anyone who suffered this mistake. I've also added a more elaborate example replacing multiple columns and added that this only works for replacing full strings, not parts of a string.