Power QueryExercises

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 table

Parameters

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

AB
0null

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

Learn more

Related functions

Official documentation: Table.ReplaceErrorValues on Microsoft Learn.