Power QueryExercises

Table.ReplaceValue

Table function · By Tahir Nas · Examples run in our simulator · Published · Updated

Replaces values in chosen columns.

When to use it

Use it to standardise codes, fix known typos or turn placeholders such as n/a into null. Replacer.ReplaceValue replaces whole cells; Replacer.ReplaceText replaces part of the text. For more than a handful of replacements, a mapping table merged into the query is easier to maintain.

Syntax

Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

Parameters

replacer
Replacer.ReplaceValue (whole cell) or Replacer.ReplaceText (inside text).

Returns

The table with replacements.

Example

Table.ReplaceValue(#table({"Channel"}, {{"Email"}, {"Phone"}}), "Email", "EM", Replacer.ReplaceValue, {"Channel"})

Result

Channel
EM
Phone

Example: replace text inside values

Replacer.ReplaceText changes every occurrence of the substring.

Table.ReplaceValue(#table({"Phone"}, {{"555-0101"}, {"555-0199"}}), "-", "", Replacer.ReplaceText, {"Phone"})

Result

Phone
5550101
5550199

Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.

Good to know

Common mistakes

The matching command in Excel

Excel for Windows
Transform > Replace Values
What you see
The dialog asks for the value to find and its replacement; Advanced options include Match entire cell contents.

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).

Exercises that use Table.ReplaceValue

Learn more

Related functions

Official documentation: Table.ReplaceValue on Microsoft Learn.