Downloads
Power Query MTable: values and fillIntermediate

Table.ReplaceErrorValues

Replaces error values in the columns you name with a value you choose.

Syntax
Table.ReplaceErrorValues(table as table, errorReplacement as list) as table

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

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

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.

Related functions

All Power Query M functions · View as Markdown · M basics