New M function: Text.ReplaceAll

Update 4 June 2019: unfortunately, the function was not meant to be published. The function is part of a certified connector and is now hidden.

The May 2019 release of Power BI Desktop includes a new M function: Text.ReplaceAll (not documented on Microsoft Docs as of 18 May 2019). This function simplifies multiple word replacements that previously had to be done by using custom functions.

Background

Replacing multiple text strings at once by using Power Query is a common scenario. Several high-profile bloggers have written their own functions to perform multiple replacements — Chris Webb, Ivan Bondarenko and Imke Feldmann, to name a few (Imke’s formula is slightly different — more on that later).

In this blog post, I’ll be using the same data as they did:

Text query

Text
the cat sat on the mat
the cat sat next to the dog
the dog chased the cat
the dog sat on the mat

let
    Source = #table(
        type table [Text = text],
        {
            {"the cat sat on the mat"},
            {"the cat sat next to the dog"},
            {"the dog chased the cat"},
            {"the dog sat on the mat"}
        }
    )
in
    Source

Replacements query

Word to Replace	Replace With
cat	bear
mat	chair
dog	dragon
the	THE

let
    Source = #table(
        type table [
            Word to Replace = text,
            Replace With = text
        ],
        {
            {"cat", "bear"},
            {"mat", "chair"},
            {"dog", "dragon"},
            {"the", "THE"}
        }
    )
in
    Source

Function syntax

Text.ReplaceAll takes two arguments:

1. Text
2. Replacements

The second argument is a list of pairs of “from” and “to” values.

For example, the following expression returns “yyz”:

Text.ReplaceAll("xyz", {{"x", "y"}})

Using Text.ReplaceAll

To use the function on Chris’s data, we need to convert the dictionary to a list of lists first. For that, we can use List.Zip. Assuming our dictionary table is in a step called Source, the following expression will give us exactly what we need:

= List.Zip({Source[Word to Replace], Source[Replace With]})

So the whole Replacements query then becomes:

let
        Source = #table(
        type table [
            Word to Replace = text,
            Replace With = text
        ],
        {
            {"cat", "bear"},
            {"mat", "chair"},
            {"dog", "dragon"},
            {"the", "THE"}
        }
    ),
    ToList = List.Zip(
        {
            Source[Word to Replace],
            Source[Replace With]
        }
    )
in
    ToList

Applying the function is then straightforward. If we add a custom column, this is the formula we can use:

= Text.ReplaceAll([Text], Replacements)

Here are the results:

Considerations

It is important to note that the function currently only replaces substrings, not whole words. I.e., if you introduce an extra row to the Replacements table like so:

Word to Replace	Replace With
cat	bear
mat	chair
dog	dragon
the	THE
air	water

The results will probably be not what you want:

In case you need to replace whole words only, I would use Imke’s approach, which addresses the issue.

Note that the function performs replacements sequentially: if you place the air-water pair at the top of the list, you will get different results.

You can download my .pbix file here: TextReplaceAll.pbix