Downloads
Power Query MTable: rowsIntermediate

Table.SelectRowsWithErrors

Keeps only the rows that contain an error in one or more columns.

Syntax
Table.SelectRowsWithErrors(table as table, optional columns as nullable list) as table

Returns: A table with the rows that have errors.

What it does

When a conversion or calculation fails, Power Query puts an error value in that cell instead of stopping the query. Table.SelectRowsWithErrors shows you those rows, so you can see the actual bad values instead of guessing. Use it as an audit step while you clean an export, and keep it in a review query so the list of problems is saved.

Syntax and arguments

Example

A bank export has an Amount column. Some amounts are text such as "N/A" or "1,234.00 CR", and they fail when the column is converted to a number.

let
    Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"Amount", type number}}),
    Problems = Table.SelectRowsWithErrors(Typed, {"Amount"})
in
    Problems

The result is only the rows whose Amount did not convert. Each one can be fixed in the bank file, or handled with a rule in the query. Errors show in the editor as "Error" in the cell, and clicking the cell shows the reason.

Things to watch

In business terms

Imports from banks, payroll systems and ERPs often contain a few rows that will not load. Listing those rows in a review query gives the preparer and the reviewer the same, written record of what was wrong and what was done about it.

Related functions

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