Table.RemoveRowsWithErrors Function in Power Query

The Table.RemoveRowsWithErrors function in Power Query, returns a table with all rows removed from the input table that contain an error in at least one of the cells in a row.

Syntax

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

The function has the following parameters:

table: The input table you want to clean.
columns: It is an optional parameter. A column name (text) or a list of column names, representing the column(s) to check for errors. If omitted, all columns are checked.

Example: Remove Errors from All Columns

Power Query M

let
    Source = Table.FromRecords({
        [Name = "Ashish", Age = 30],
        [Name = "Katrina", Age = 28],
        [Name = "Alia", Age = Error.Record("AgeError", "Invalid age")]
    }),
    Cleaned = Table.RemoveRowsWithErrors(Source)
in
    Cleaned

The output of the above code is shown below:

Power Query M

Table.FromRecords({
    [Name = "Ashish", Age = 30],
    [Name = "Katrina", Age = 28]
})

Example: Remove Errors Only in a Specific Column

Power Query M

let
    Source = Table.FromRecords({
        [Name = "Ashish", Age = 30],
        [Name = "Katrina", Age = Error.Record("AgeError", "Invalid age")],
        [Name = Error.Record("NameError", "Missing name"), Age = 25]
    }),
    Cleaned = Table.RemoveRowsWithErrors(Source, {"Age"})
in
    Cleaned

The output of the above code is shown below:

Power Query M

Table.FromRecords({
    [Name = "Ashish", Age = 30],
    [Name = Error.Record("NameError", "Missing name"), Age = 25]
})

Only rows where the Age column contains an error are removed.
Errors in the Name column are retained.

Previous Next