Table.ReplaceErrorValues
Puts a value in place of every error in the columns you name.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.ReplaceErrorValues(table as table, errorReplacement as list) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table with errors in its cells. |
errorReplacement | list | One pair {column, value}, or a list of pairs. |
Returns
table. The same rows; the errors in those columns now hold the values given.
Remarks
- Cells without an error keep their values.
- This is the step Replace Errors writes in Transform data. To drop such rows instead, use
Table.RemoveRowsWithErrors.
Examples
A price that wasn't a number becomes 0
let
Typed = Table.TransformColumnTypes(#table({"Product", "Price"}, {{"Chair", "50"}, {"Desk", "n/a"}}), {{"Price", type number}})
in
Table.ReplaceErrorValues(Typed, {"Price", 0})| Product | Price |
|---|---|
| Chair | 50 |
| Desk | 0 |
A value per column
let
Typed = Table.TransformColumnTypes(
#table({"Product", "Price", "Units"}, {{"Chair", "50", "x"}, {"Desk", "n/a", "2"}, {"Lamp", "30", "1"}}),
{{"Price", type number}, {"Units", Int64.Type}})
in
Table.ReplaceErrorValues(Typed, {{"Price", 0}, {"Units", 1}})| Product | Price | Units |
|---|---|---|
| Chair | 50 | 1 |
| Desk | 0 | 2 |
| Lamp | 30 | 1 |
Related functions
Table.RemoveRowsWithErrors, Table.SelectRowsWithErrors, Table.ReplaceValue.