Table.SelectRowsWithErrors
Keeps only the rows that contain an error in one or more columns.
Table.SelectRowsWithErrors(table as table, optional columns as nullable list) as tableReturns: 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
- table: the table to check. Required.
- columns: an optional list of column names to check. If you leave it out, every column is checked.
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
- Blank cells are not errors. A blank is null, so this step will not find it. Use
Table.SelectRowswith= nullfor blanks. - Without a column list, every column is checked. In a wide table one bad column can fill the result, so pass the columns you care about.
- Do not delete the problem rows without a note. Removing them changes the totals in your report.
- Query folding: this step usually does not fold. Power Query evaluates the errors itself, so put it after the rows you need are already filtered.
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.