Table.ReplaceErrorValues
Replaces error values in the columns you name with a value you choose.
Table.ReplaceErrorValues(table as table, errorReplacement as list) as tableReturns: The table with its error cells replaced.
What it does
When a cell holds an error, Table.ReplaceErrorValues puts a value in its place, so the row stays in the table. Use it when the row matters but the bad value cannot be read. Be careful with the replacement. Zero looks like a real amount, so a flag is usually better than a made-up figure.
Syntax and arguments
- table: the table to change. Required.
- errorReplacement: a list of pairs. Each pair is a column name and the value to use for its errors, for example
{{"Amount", null}}. Required.
Example
Bank amounts that fail to convert are replaced with null, not zero, and a Check column flags them for the reviewer.
let
Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
Typed = Table.TransformColumnTypes(Source, {{"Amount", type number}}),
NoErrors = Table.ReplaceErrorValues(Typed, {{"Amount", null}}),
Flagged = Table.AddColumn(NoErrors, "Check", each if [Amount] = null then "Amount needs review" else "", type text)
in
Flagged
The row stays in the table with a blank amount and a clear note. The reviewer can find every flagged row with a filter on the Check column.
Things to watch
- Replacing an error with zero hides the problem. Use null and a flag.
- A blank cell is not an error. Table.ReplaceValue handles blanks.
- The replacement value must suit the column type. Null works for any type.
- Query folding: this step breaks folding, because Power Query applies it to the loaded rows. Place it late in the query, after the filters.
In business terms
An amount that cannot be read should never quietly turn into a zero in a reconciliation. Keeping the row with a visible flag keeps the exception in front of the reviewer.