Text.Replace - Text Function

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

The Text.Replace function in Power Query M replaces all occurrences of a given text value (old) with a specified value (new). This function is case-sensitive, meaning it distinguishes between ‘A’ and ‘a’. The text parameter refers to the original string where you want to make replacements, old is the text you want to replace, and new is the text you want to use instead.

Examples

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

Replacing a Word in a String

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

Replacing All Occurrences of a Word

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

Switching Perspectives in a Sentence

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

Notice that the 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:

Replace Values in Power Query M (Ultimate Guide)
Learn how to replace values in Power Query. Look into conditional replacements, replacing in multiple columns, case sensitivity and more! » Read more
Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more

Other functions related to Text.Replace are:

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