Downloads
Power Query MTable: rowsIntermediate

Table.RemoveRowsWithErrors

Drops every row that has an error in one or more of the columns you check.

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

Returns: A table without the rows that had errors.

What it does

Table.RemoveRowsWithErrors deletes any row where a checked column holds an error, such as a value that could not be converted to a number. It is a quick way to get a clean table, but it removes rows without telling anyone, so the totals change. Use it only after you have saved a list of the problem rows.

Syntax and arguments

Example

A payroll export has an Amount column. Rows whose amounts cannot be read are removed after the list of them has been kept in a separate review query.

let
    Source = Excel.CurrentWorkbook(){[Name = "Payroll"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"Amount", type number}}),
    Clean = Table.RemoveRowsWithErrors(Typed, {"Amount"})
in
    Clean

The Amount column is converted first, so the bad values become errors, and then the rows with errors are dropped. The reviewer can use a separate query built on Table.SelectRowsWithErrors to see what was removed.

Things to watch

In business terms

Reconciliations need to show what was excluded from an import and why. Removing error rows is acceptable when the exclusions are listed, counted and approved, and a clean file without that list is not.

Related functions

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