Table.RemoveRowsWithErrors

What is Table.RemoveRowsWithErrors?

Table.RemoveRowsWithErrors is a function in Power Query M that allows you to remove rows from a table that contain errors. The function takes a table as its input and returns a new table with the rows that contain errors removed.

The function is particularly useful when dealing with large datasets that contain errors or invalid data. By removing these rows, you can ensure that your analysis is based on clean and accurate data.

How to Use Table.RemoveRowsWithErrors

To use the Table.RemoveRowsWithErrors function in Power Query M, you first need to load your data into Power Query. Once you have done this, you can follow these steps:

1. Select the column that contains the errors you want to remove.

2. Click on the “Remove Errors” button in the “Transform” tab.

3. Choose the option “Remove Rows with Errors”.

4. Click on “OK”.

Once you have completed these steps, Power Query M will automatically generate the M code for the Table.RemoveRowsWithErrors function. You can then edit the code if necessary or simply click “Close & Apply” to apply the changes to your data.

The M Code Behind Table.RemoveRowsWithErrors

The M code behind the Table.RemoveRowsWithErrors function is relatively simple and consists of a single line of code:

Here’s a breakdown of what each part of the code does:

– `Table.RemoveRowsWithErrors` is the name of the function.

– `table as table` specifies that the input to the function is a table.

– `optional errorHandling as nullable number` indicates that the function has an optional parameter that allows you to specify how errors should be handled. This parameter is not required and can be left blank.

Customizing the Error Handling Behavior

As mentioned earlier, the Table.RemoveRowsWithErrors function has an optional parameter that allows you to specify how errors should be handled. By default, the function removes any rows that contain errors. However, you can customize the error handling behavior by specifying one of the following values for the `errorHandling` parameter:

– `0`: Remove rows that contain errors (default behavior).

– `1`: Replace errors with null values.

– `2`: Replace errors with default values.

For example, if you want to replace errors with null values instead of removing the rows, you can modify the M code as follows:

In conclusion, Table.RemoveRowsWithErrors is a useful function in Power Query M that can help you clean and transform large datasets with ease. By understanding the M code behind the function and customizing the error handling behavior, you can ensure that your analysis is based on clean and accurate data.

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

Upcoming Courses

Contact Us