Text.Replace Function in Power Query M (5 Easy Examples)

Text.Replace swaps every occurrence of one piece of text for another inside a text value. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you need to clean up a column by trading one substring for another, like turning a status word into a different one or stripping a prefix, this is the function you reach for.

Table of Contents

Syntax of Text.Replace Function

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

where

text (required, nullable text). The text value you want to search inside.
old (required, text). The substring to find. Every match in text gets replaced.
new (required, text). The substring to put in place of each old match. Use "" to remove old entirely.

Returns: a text value with all occurrences of old replaced by new. The match is case-sensitive. If text is null, it returns null.

In plain terms, you give it some text, the bit to find, and the bit to use instead, and it hands back the text with every match swapped out.

Example 1: Replace one word in a string

Swap the word Pending for Shipped in an order status.

Text.Replace("Order Pending","Pending","Shipped")

Result: Order Shipped

The single match is found and replaced, so the status reads Order Shipped.

Example 2: It replaces every occurrence, not just the first

Here the value green shows up twice.

Text.Replace("red-green-blue-green","green","yellow")

Result: red-yellow-blue-yellow

Both green matches are swapped for yellow. Text.Replace does not stop after the first hit.

Example 3: Matching is case-sensitive

The text has three spellings of the same word in different cases.

Text.Replace("USA usa Usa","usa","UK")

Result: USA UK Usa

Only the lowercase usa matches. USA and Usa are left alone because the comparison respects case.

Example 4: Strip a prefix from a column in place

Say you have a Teams query with a Code and a Department column, where every department is tagged with a Dept- prefix.

You want to drop that prefix and keep the column where it is.

Here is the starting data:

Code	Department
TM-A	Dept-Sales
TM-B	Dept-HR
TM-C	Dept-Finance

Use Table.TransformColumns to run Text.Replace on each value in the Department column:

let
    Source = Excel.CurrentWorkbook(){[Name="Teams"]}[Content],
    Cleaned = Table.TransformColumns(Source,{{"Department",each Text.Replace(_,"Dept-",""),type text}})
in
    Cleaned

Replacing Dept- with "" removes the prefix from every value.

The result keeps the same columns with clean department names:

Code	Department
TM-A	Sales
TM-B	HR
TM-C	Finance

The Code column is untouched because the transform only targets Department.

Example 5: Swap a separator into a new column

Say you have a Paths query with a Folder and a Location column, where each location is a Windows file path using backslashes.

You want a web-style version of each path that uses forward slashes, kept in a new column.

Here is the starting data:

Folder	Location
Reports	C:\Users\Sam\Reports
Exports	C:\Users\Sam\Exports

Add a WebPath column that replaces each \ with /:

let
    Source = Excel.CurrentWorkbook(){[Name="Paths"]}[Content],
    Added = Table.AddColumn(Source,"WebPath",each Text.Replace([Location],"\","/"),type text)
in
    Added

Every backslash in Location becomes a forward slash in the new column.

The result adds the WebPath column alongside the originals:

Folder	Location	WebPath
Reports	C:\Users\Sam\Reports	C:/Users/Sam/Reports
Exports	C:\Users\Sam\Exports	C:/Users/Sam/Exports

The original Location stays as is, so you keep both the source and the converted path.

Things to keep in mind with Text.Replace

It replaces literal text, not patterns or wildcards. "a*" looks for the actual characters a*. For pattern-style work use Splitter functions, or Text.Contains to test for a substring before deciding what to do.
To replace by position rather than by content, use Text.ReplaceRange. Text.Replace finds matches by their text, not by where they sit in the string.
Both old and new must be text. Passing a number throws Expression.Error: We cannot convert the value 5 to type Text. Convert it first with Text.From.

Common questions about Text.Replace

What is the difference between Text.Replace and the Replace Values button in the Power Query UI?

The Replace Values button writes a Table.ReplaceValue step that works on whole column values and can match the entire cell. Text.Replace works inside a single text value and swaps substrings wherever they appear, which is why you wrap it in Table.TransformColumns or Table.AddColumn for a column.

How do I do a case-insensitive replace?

Text.Replace has no case-insensitive option. The usual workaround is to normalize the case first, for example Text.Replace(Text.Lower([Column]),"old","new"), accepting that the output is lowercased.

List of All Power Query Functions

Related Power Query Functions / Articles: