Table.RemoveRowsWithErrors
Drops every row that has an error in one or more of the columns you check.
Table.RemoveRowsWithErrors(table as table, optional columns as nullable list) as tableReturns: 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
- table: the table to clean. Required.
- columns: an optional list of column names to check. If you leave it out, every column is checked.
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
- A row is removed if any checked column has an error. List only the columns you need, so a harmless column does not remove real data.
- Removing rows changes totals. Compare the row count and the total before and after the step, and record the difference.
- If the rows matter but the value is unknown, use Table.ReplaceErrorValues instead, and flag the cell.
- Query folding: this step does not fold. Power Query applies it to the rows it has loaded.
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.