Table.RemoveRowsWithErrors
The table without the rows that have an error in a cell: in any column, or in the columns you name.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.RemoveRowsWithErrors(table as table, optional columns as nullable list) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table to clean. |
columns | nullable list optional | The columns to check; every column when left out. |
Returns
table. The rows whose checked cells all hold values.
Remarks
- Errors in cells mostly come from a type change that failed, such as text that isn't a number. This is the step Remove Errors writes in Transform data.
- Rows with an error in a column you didn't name stay, error and all.
Examples
Prices that weren't numbers
let
Typed = Table.TransformColumnTypes(#table({"Product", "Price"}, {{"Chair", "50"}, {"Desk", "n/a"}}), {{"Price", type number}})
in
Table.RemoveRowsWithErrors(Typed)| Product | Price |
|---|---|
| Chair | 50 |
Checking one column only
Chair's units are an error, Desk's price is one. Only Units is checked, so Desk stays; the example shows the products left.
let
Typed = Table.TransformColumnTypes(
#table({"Product", "Price", "Units"}, {{"Chair", "50", "x"}, {"Desk", "n/a", "2"}, {"Lamp", "30", "1"}}),
{{"Price", type number}, {"Units", Int64.Type}})
in
Table.RemoveRowsWithErrors(Typed, {"Units"})[Product]{"Desk", "Lamp"}Related functions
Table.SelectRowsWithErrors, Table.ReplaceErrorValues, Table.TransformColumnTypes.