Table.ReplaceErrorValues
TableReplaces error values in specified columns with replacement values.
Syntax
Table.ReplaceErrorValues(table as table, errorReplacement as list) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
table | table | Yes | The table to clean. |
errorReplacement | list | Yes | A list of {columnName, replacementValue} pairs specifying the column and value to substitute for errors. |
Return Value
table — The table with error values in specified columns replaced by the given values.
Remarks
Table.ReplaceErrorValues substitutes error values in specified columns with a safe replacement value. Each element of errorReplacement is a two-element list: {columnName, replacementValue}. Only the explicitly listed columns are modified — errors in unlisted columns are left in place.
This function is most commonly used immediately after Table.TransformColumnTypes when type conversion fails for some rows (e.g., non-numeric text in a number column produces an error). Replacing those errors with 0, null, or a sentinel value like "N/A" keeps the dataset processable. It is more targeted than Table.RemoveRowsWithErrors, which discards the entire row.
You can also use try ... otherwise inside Table.AddColumn or Table.TransformColumns for single-column error handling, but Table.ReplaceErrorValues is simpler when replacing errors across multiple columns at once.
Examples
Example 1: Fix type-conversion errors in the Salary column after a type change
let
Employees = #table(
{"EmployeeID", "FullName", "Salary"},
{{"E001","alice smith","55000"},{"E002","BOB JONES","N/A"},{"E003","Charlie Brown","72000"},{"E006","frank lee","unknown"}}
),
Typed = Table.TransformColumnTypes(Employees, {{"Salary", type number}}),
Cleaned = Table.ReplaceErrorValues(Typed, {{"Salary", 0}})
in
CleanedEmployeeID | FullName | Salary | |
|---|---|---|---|
| 1 | E001 | alice smith | 55,000 |
| 2 | E002 | BOB JONES | 0 |
| 3 | E003 | Charlie Brown | 72,000 |
| 4 | E006 | frank lee | 0 |
Example 2: Replace errors across multiple columns simultaneously
let
Sales = #table(
{"OrderID", "CustomerName", "UnitPrice", "Quantity"},
{{1,"Alice",25.00,4},{2,error "missing",50.00,2},{3,"Charlie",error "bad value",10},{4,"Alice",75.00,error "NaN"}}
)
in
Table.ReplaceErrorValues(Sales, {{"CustomerName","Unknown"},{"UnitPrice",0},{"Quantity",0}})OrderID | CustomerName | UnitPrice | Quantity | |
|---|---|---|---|---|
| 1 | 1 | Alice | 25 | 4 |
| 2 | 2 | Unknown | 50 | 2 |
| 3 | 3 | Charlie | 0 | 10 |
| 4 | 4 | Alice | 75 | 0 |
Example 3: Replace price errors with null to distinguish missing from zero
let
Products = #table(
{"ProductID","ProductName","Price"},
{{1,"Widget A",25.00},{2,"Gadget B",error "TBD"},{3,"Widget C",15.00},{5,"Thingamajig E",error "unlisted"}}
)
in
Table.ReplaceErrorValues(Products, {{"Price", null}})ProductID | ProductName | Price | |
|---|---|---|---|
| 1 | 1 | Widget A | 25 |
| 2 | 2 | Gadget B | null |
| 3 | 3 | Widget C | 15 |
| 4 | 5 | Thingamajig E | null |