Replacer.ReplaceText - Replacer Function

Replacer.ReplaceText is a Power Query M function that replaces old text in the original text with new text. The function returns a replacer function for use in List.ReplaceValue and Table.ReplaceValue.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Replacer.ReplaceText(
   text as nullable text,
   old as text,
   new as text,
) as nullable text

Description

The Replacer.ReplaceText function is designed to replace specified text values within a string. It can be used on its own for simple replacements or integrated into other functions like List.ReplaceValue and Table.ReplaceValue for more complex data transformations. When the function receives a null value, the function returns null.

Examples

Let’s see how the Replacer.ReplaceText function works.

Simple Text Replacement

The primary use of Replacer.ReplaceText is to substitute specified text values. Consider the following expression:

// Output: // "The cat scratched the lady"
Replacer.ReplaceText( "The cat scratched the man", "man", "lady" )

In this case, the word “man” is replaced by “lady”, resulting in the output:

Handling Null Values

The function can handle null values gracefully which is also suggest by definition of the first parameter. It is of type ‘nullable text’. If the input text is null, the output will also be null. For example:

// Output: "Null"
Replacer.ReplaceText( null, "man", "lady" )

Using Replacer.ReplaceText in Other Functions

Replacer.ReplaceText can be combined with other functions to enhance their functionality. One such example is the List.ReplaceValue function, which replaces text values within a list.

Example with List.ReplaceValue

Suppose we have a list with some spelling errors:

{ "Fish", "Mat", "Mamel" }

We want to correct the spellings so that “Mat” becomes “Cat” and “Mamel” becomes “Camel”. This can be done using the following expression:

// Replaces with partial match { "Fish", "Cat", "Camel" }
List.ReplaceValue( 
  { "Fish", "Mat", "Mamel" }, 
  "M, 
  "C",
  Replacer.ReplaceText 
)

This expression performs partial matches and replaces “M” with “C”.

Example with Table.ReplaceValue

You can also use Replacer.ReplaceText to replace values within a table. Let’s define a simple table for demonstration purposes:

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTc1T0oHSsTpQkfLM5AyYKJgNl1EIzsgsKoHJQXkg2XSoSRA6NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ReplaceText = _t, ReplaceValue = _t])

Consider above table where we want to replace the text “Green” in the ReplaceText column with “Yellow”. This can be achieved using:

Table.ReplaceValue(
  Source, 
  "Green", 
  "Yellow",
  Replacer.ReplaceText, 
  {"ReplaceText"} 
)

This expression swaps all instances of “Green” with “Yellow”, transforming the data as intended. The below table is the result:

Table.ReplaceValue differences between Replacer.ReplaceText and Replacer.ReplaceValue

Used by

While you can use the Replacer.ReplaceText function by itself, it also works together with:

Other functions related to Replacer.ReplaceText are:

Replacer.ReplaceValue

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/replacer-replacetext