Replacer.ReplaceText
Replaces part of a text value, every place where the old text appears, with the new text.
Replacer.ReplaceText(text, old, new)Returns: The changed text.
What it does
Replacer.ReplaceText changes text inside a value. Where Replacer.ReplaceValue swaps a whole value, this one swaps a piece of it, such as removing a dollar sign or a thousands separator from every amount in a column. It is most often passed to Table.ReplaceValue or List.ReplaceValue, which call it for each value.
Syntax and arguments
text: the text value being checked. Power Query supplies this when you pass the function to a replace step.old: the text to look for, such as"$".new: the text to put in its place, such as""to remove it.
Example
Amounts were exported with a dollar sign and thousands separators, such as "$1,250.00". The team wants clean text that can be converted to numbers.
let
Amounts = {"$1,250.00", "$480.50", "$75.00"},
NoDollar = List.ReplaceValue(Amounts, "$", "", Replacer.ReplaceText),
NoComma = List.ReplaceValue(NoDollar, ",", "", Replacer.ReplaceText),
Numbers = List.Transform(NoComma, Number.FromText)
in
Numbers
Numbers is 1250, 480.5 and 75. Two replacements are needed because each one removes one kind of mark. The conversion comes last, when the text is clean.
Example: a second, different use
Removing a prefix from account codes in a table column, using Table.ReplaceValue with Replacer.ReplaceText on the codes column.
Things to watch
- Replacement is by text, not by pattern. For pattern matching use Text.Remove or Text.Select on the characters you want.
- Replacing text inside a value can also change things you did not mean to change. Test on a sample.
- Use it before converting to numbers. The conversion fails on symbols that are still in the text.
- A null value passes through as null.
In business terms
Stripping currency symbols, thousands separators and stray labels from exported amounts is one of the most common cleanup steps in finance data work. Keep each replacement visible, so a reviewer can see what was changed.