Table.ReplaceErrorValues
Table function · By Tahir Nas · Examples run in our simulator · Published · Updated
Replaces errors in columns with a value.
When to use it
Use it to replace cell errors with a value (often null) so a load succeeds while the row stays visible. Replacing with 0 hides the problem in totals; null shows it as missing.
Syntax
Table.ReplaceErrorValues(table as table, errorReplacement as list) as tableParameters
- errorReplacement
- A list of {column, value} pairs.
Returns
The table with errors replaced.
Example
Table.ReplaceErrorValues(Table.TransformColumnTypes(#table({"Qty"}, {{"3"}, {"n/a"}}), {{"Qty", Int64.Type}}), {{"Qty", null}})Result
| Qty |
|---|
| 3 |
| null |
Example: replace errors in two columns
Each column can get its own replacement.
Table.ReplaceErrorValues(Table.TransformColumnTypes(#table({"A", "B"}, {{"x", "y"}}), {{"A", type number}, {"B", type number}}), {{"A", 0}, {"B", null}})Result
| A | B |
|---|---|
| 0 | null |
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 keeps the gap visible; 0 hides it.
Common mistakes
- Using 0 so bad values flow into sums as real zeros.
- Replacing errors that signal a culture problem instead of fixing the conversion.
The matching command in Excel
- Excel for Windows
- Select the column > Transform > Replace Values > Replace Errors
- What you see
- Every error in the column is replaced by the value you enter.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Dealing with errors in Power Query (Microsoft Learn).
Exercises that use Table.ReplaceErrorValues
- intermediate~8 minPro
Replace bad quantities with null and flag them (kite shop)
Keep every order, but turn unreadable quantities into null and add a Needs Review flag.
- intermediate~8 minPro
Replace conversion errors with a different value per column (garden centre)
After typing, bad Discount values should become 0 and bad Price values null. One step can do both.
Learn more
Related functions
Official documentation: Table.ReplaceErrorValues on Microsoft Learn.