Table.SelectRowsWithErrors - Table Function

Table.SelectRowsWithErrors is a Power Query M function that returns a table with rows that contain errors in at least one of the cells, with an optional columns list to inspect specific columns for errors. The function returns a table with the rows containing errors.

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

Syntax

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

Description

Table.SelectRowsWithErrors returns a table with rows from the input table that contain errors in at least one cell. If a column list is specified, only those columns are checked for errors.

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.SelectRowsWithErrors base table with two errors in Power Query M

Selecting All Rows with Errors

By default, the Table.SelectRowsWithErrors returns rows that have an error in any of its columns. To select all rows with errors in your table, simply provide the table (in this case, Source) as the only argument as follows:

Table.SelectRowsWithErrors( Source )

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

Table.SelectRowsWithErrors returns error rows from all columns in Power Query M

Selecting Rows with Errors from Specific Columns

In some cases, you may only want to select rows with errors from specific columns. For example, if you only want to keep 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:

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

This operation will only select rows where the OrderDate column contains an error, leaving out rows with errors in other columns (such as ProductKey). Here’s the result:

Table.SelectRowsWithErrors returns error rows 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 }
    }
),
    SelectErrorRows = Table.SelectRowsWithErrors( Source ),
    SelectErrorsFromOrderDate = Table.SelectRowsWithErrors( Source, { "OrderDate" } )

in
    SelectErrorsFromOrderDate

Other functions related to Table.SelectRowsWithErrors are:

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