Table.SelectRowsWithErrors

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.SelectRowsWithErrors

What is Table.SelectRowsWithErrors?

The Table.SelectRowsWithErrors function is used to filter a table and return only the rows that contain errors. It takes a table as an input and returns a table with only the rows that have errors. This function is useful when you have a large dataset and want to quickly identify and fix errors.

Syntax of Table.SelectRowsWithErrors

The syntax of Table.SelectRowsWithErrors is as follows:

Table.SelectRowsWithErrors(table as table, optional errorHandling as nullable number) as table

The `table` parameter is the input table that you want to filter. The `errorHandling` parameter is optional and specifies how errors should be handled. If you don’t specify anything, the default value is 1, which means that errors will be replaced with null values.

How Table.SelectRowsWithErrors Works

Table.SelectRowsWithErrors works by using the Table.TransformRows function to iterate through each row of the input table and check for errors. If a row contains an error, it is added to a new table that is returned as the output.

The function checks for errors in each column of the table. If a column contains an error, the entire row is considered to contain an error. Errors can occur in various forms, such as #VALUE!, #REF!, #DIV/0!, #NAME?, and #NUM!.

How to Use Table.SelectRowsWithErrors

To use Table.SelectRowsWithErrors, follow these steps:

1. Open Power Query and create a new blank query.

2. Go to the Home tab and select “Advanced Editor”.

3. In the editor, enter the following code:

let

Source = [Enter your data source here],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type number}, {"Column3", type date}}),

#"Filtered Rows" = Table.SelectRowsWithErrors(#"Changed Type", 1)

in

#"Filtered Rows"

4. Replace “Enter your data source here” with the name of your data source.

5. Replace “Column1”, “Column2”, and “Column3” with the names of the columns you want to filter.

6. Press the “Done” button to close the editor and apply the changes.

The above code will filter the input table and return only the rows that contain errors. The `errorHandling` parameter is set to 1, which means that errors will be replaced with null values.

The Table.SelectRowsWithErrors function is a powerful tool for cleaning up data in Power Query. It allows users to quickly identify and fix errors in large datasets. By understanding the M code behind this function, you can customize it to your specific needs and make your data transformation process more efficient.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)