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 tableParameters
- 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
- null and "" are different search values.
Common mistakes
- Using ReplaceText for whole values, so "Email" also changes "Emails".
- Many chained replace steps where a mapping table would be clearer.
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
- intermediate~7 minFree
Treat empty text and null the same (hardware)
Some suppliers are null and some are empty text. Standardise both to "Unknown".
- beginner~6 minPro
Replace channel names with short codes (telecom)
Turn Email, Phone and Chat into EM, PH and CH for a compact dashboard.
- intermediate~7 minPro
Fill down labels whose blanks are empty text (volunteers)
Fill Down does nothing on this roster because the blank cells contain empty text, not null. Fix that first.
- advanced~10 minPro
Predict which cells an unpivot will drop (stock sheet)
Before unpivoting a sheet with blank (null) cells, list exactly the item–month cells that will disappear.
- advanced~13 minPro
Fill in missing store–month combinations with zero (kiosks)
Stores with no sales in a month have no row at all, so charts skip them. Produce every store × month pair, with 0 where there were no sales.
- advanced~15 minPro
Debug a city revenue report with three bugs (kite shop)
The starter runs, but its numbers are wrong in three places. Find and fix each bug.
Learn more
- Replace values exactly lesson
- null, empty text and errors are different things lesson
- Power Query data cleaning recipes for messy exports guide
Related functions
Official documentation: Table.ReplaceValue on Microsoft Learn.