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 textDescription
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 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" )
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" )
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.
Related articles
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
Related functions
Other functions related to Text.Replace are:
