Replace values exactly
Replace values without accidentally changing partial matches.
~8 min read · beginner · Module 6: Text transformations
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Trim, clean and capitalise text
Table.ReplaceValue has a replacer argument that decides what counts as a match. Choosing the wrong one is the most common source of surprise in this step:
Replacer.ReplaceValuereplaces a cell only when the whole value equals the search value.Replacer.ReplaceTextreplaces the search text wherever it appears inside text.
let
Source = #table({"Channel"}, {{"Web"}, {"Webchat"}, {"Phone"}}),
WholeValue = Table.ReplaceValue(Source, "Web", "Online", Replacer.ReplaceValue, {"Channel"}),
AsText = Table.ReplaceValue(Source, "Web", "Online", Replacer.ReplaceText, {"Channel"})
in
Table.FromColumns({Source[Channel], WholeValue[Channel], AsText[Channel]}, {"Original", "ReplaceValue", "ReplaceText"})Result
| Original | ReplaceValue | ReplaceText |
|---|---|---|
| Web | Online | Online |
| Webchat | Webchat | Onlinechat |
| Phone | Phone | Phone |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
In the editor, ticking Match entire cell contents writes ReplaceValue; leaving it unticked writes ReplaceText. For codes and categories you almost always want the whole-value match. Text replacement is right for fixing a character everywhere, such as replacing "/" with "-" in references.
Many replacements
Several Replace Values steps are fine for a handful of values. For a longer mapping, one conditional transform is easier to read and review, and it can send unknown values to a default:
let
Source = #table({"Channel"}, {{"Email"}, {"Phone"}, {"Chat"}, {"Fax"}}),
Coded = Table.TransformColumns(Source, {{"Channel", each if _ = "Email" then "EM" else if _ = "Phone" then "PH" else if _ = "Chat" then "CH" else "OTHER", type text}})
in
CodedResult
| Channel |
|---|
| EM |
| PH |
| CH |
| OTHER |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
When the mapping is long or changes often, keep it in a small table and merge it in (module 12). Business users can then update the mapping without editing M.
Replacing null
Replace Values can also replace null: search for null with Replacer.ReplaceValue. An empty search box in the editor means empty text, not null, which is a frequent surprise (see null vs empty text).
Where to find this in Excel
- Excel for Windows
- Transform > Replace Values (Advanced options > Match entire cell contents)
- What you see
- On text columns the default replaces every occurrence inside a cell; Match entire cell contents only replaces whole values.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Replace values and errors (Microsoft Learn).
Common mistakes
- Replacing a short code as text and changing every value that contains it (Web → Online also turns Webchat into Onlinechat).
- Leaving the search box empty to replace nulls, which only replaces empty text.
- Chaining dozens of Replace Values steps instead of a mapping table.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: Transform → Replace Values; the Advanced options section has Match entire cell contents and Replace using special characters.
- Replace using special characters lets you insert tabs and line feeds, written as
#(tab)and#(lf)in the M. - The simulator supports both replacers on text, number and null values.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Replace channel names with short codes (telecom)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.