Text.Replace

Updated on

Text.Replace is a Power Query M function that replaces all occurrences of a specified old text value with a new text value in the given text. The function returns the modified text value with the specified replacements, and is case sensitive.

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

Syntax

Text.Replace(
   text as nullable text,
   old as text,
   new as text,
) as nullable text

Description

Returns the result of replacing all occurrences of text value old in text value text with text value new. This function is case sensitive.

In this function, ‘text’ refers to the original string where you want to make replacements. The ‘old’ parameter stands for the text you want to replace, and ‘new’ is the text you want to use instead. One thing to remember is that this function is case-sensitive. It distinguishes between ‘A’ and ‘a’.

Examples

To fully understand Text.Replace, let’s delve into a few examples.

Imagine you have the string “I enjoy sunny days”, but you want to express a stronger sentiment. You can use Text.Replace to substitute the word “enjoy” with “love”.

Text.Replace( "I enjoy sunny days", "enjoy", "love" ) 
Text.Replace replaces text in Power Query M

Text.Replace replaces all occurrences of a specified text value and replaces these with a new text value. You can easily replace all occurrences of the word “I” by the word “You”.

Text.Replace( "I doubt it, I think you are right.", "I", "You" )
Text.Replace replaces all instances in Power Query M

Let’s say you have a sentence “My friend sees my show” and you want to switch the perspective from first-person to third-person. Text.Replace can make this switch for you:

Text.Replace( "My friend sees my show", "My", "His" )
Text.Replace replaces case sensitive in Power Query M

Take note, as this example demonstrates, Text.Replace is case sensitive. It replaced “My” but not “my”.

In conclusion, Text.Replace is great when you need to replace text within a string. It performs case-sensitive replacements, ensuring you have complete control over your text data manipulations.

Learn more about Text.Replace in the following articles:

Other functions related to Text.Replace are:

BI Gorilla Youtube Channel

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