Downloads
Power Query MTextBeginner

Text.Replace

Replaces every occurrence of one piece of text with another.

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

Returns: The text with each match replaced, or null when the input is null.

What it does

Text.Replace swaps every match of one piece of text for another. Replacing with an empty text removes the piece entirely. It is the usual first step when an amount arrives as text with commas or currency signs in it, because the text has to be cleaned before it can become a number.

Syntax and arguments

Example

A bank export shows amounts as text with thousands separators and a dollar sign. The team strips the extra characters, then converts the result to a number.

let
    Raw = "$1,234.50",
    NoSymbol = Text.Replace(Raw, "$", ""),
    NoComma = Text.Replace(NoSymbol, ",", ""),
    Amount = Number.FromText(NoComma, "en-US")
in
    Amount

NoComma is "1234.50" and Amount is the number 1234.5. Each step is shown so a reviewer can see exactly what changed.

Example: a second, different use

Replace the dashes in a bank reference with nothing so two references written in different formats can be compared: "INV-1043" becomes "INV1043".

Things to watch

In business terms

Amounts, phone numbers and reference codes exported as text need cleaning before any total can be made. Text.Replace followed by a number conversion is how a bank or processor feed becomes a column that sums correctly.

Related functions

All Power Query M functions · View as Markdown · M basics