Power QueryExercises

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.ReplaceValue replaces a cell only when the whole value equals the search value.
  • Replacer.ReplaceText replaces 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

OriginalReplaceValueReplaceText
WebOnlineOnline
WebchatWebchatOnlinechat
PhonePhonePhone

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Text replacement turned Webchat into Onlinechat.

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
    Coded

Result

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.
Functions in this lesson:Table.ReplaceValueReplacer.ReplaceValueReplacer.ReplaceText

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.