Table.RemoveRowsWithErrors - Table Function

Table.RemoveRowsWithErrors is a Power Query M function that removes rows containing errors in a table, optionally limited to specified columns. The function returns a new table with the error-containing rows removed.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

The Table.RemoveRowsWithErrors function removes rows containing errors, either across the entire table or in specified columns.

Examples

Imagine we have a table called Source that contains two rows with errors. In this table:

One error is in the ProductKey column.
Another error is in the OrderDate column.

Here’s what the table looks like:

Table.RemoveRowsWithErrors table with two errors in Power Query M

Removing All Rows with Errors

The Table.RemoveRowsWithErrors function is a quick solution for removing any row that has an error in any column. To remove all rows with errors in your table, simply provide the table (in this case, Source) as the only argument as follows:

Table.RemoveRowsWithErrors( Source )

This operation will remove all rows in Source where any column contains an error. After running this function, the resulting table no longer includes the rows with errors in ProductKey and OrderDate:

Table.RemoveRowsWithErrors removes two error rows in Power Query M

Removing Rows with Errors from Specific Columns

In some cases, you may only want to remove rows with errors from specific columns. For example, if you only want to remove rows where there’s an error in the OrderDate column (but are okay with errors in ProductKey), you can specify this column’s name.

To do this, pass a list containing the column name(s) as the second argument to Table.RemoveRowsWithErrors:

Table.RemoveRowsWithErrors( Source, { "OrderDate" } )

This operation removes only the rows where the OrderDate column contains an error, leaving any rows with errors in other columns (like ProductKey) unaffected.

Table.RemoveRowsWithErrors removes errors from specified column in Power Query M

Try it yourself

To try this yourself, paste the following code in the advanced editor:

let
    Source = #table(
    type table[ProductKey = Int64.Type, Product = Text.Type, OrderDate = Date.Type, Sales = Int64.Type ],
    {
        {1/"e", "Product A", #date(2024, 1, 15),        150 },
        {2,     "Product B", #date(2024, 2, 20) + 4,    200 },
        {3,     "Product C", #date(2024, 3, 10),        175 },
        {4,     "Product D", #date(2024, 4, 25),        220 },
        {5,     "Product E", #date(2024, 5, 5 ),        185 },
        {6,     "Product F", #date(2024, 6, 15),        240 }
    }
),
    RemoveErrorRows = Table.RemoveRowsWithErrors( Source ),
    RemoveErrorsFromOrderDate = Table.RemoveRowsWithErrors( Source, { "OrderDate" } )

in
    RemoveErrorsFromOrderDate

Other functions related to Table.RemoveRowsWithErrors are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-removerowswitherrors