Table.RemoveMatchingRows

Understanding the Table.RemoveMatchingRows Function

The Table.RemoveMatchingRows function is used to remove rows from a table that match a certain criteria. The syntax for this function is as follows:

In this syntax, the first argument (table as table) is the input table from which the rows need to be removed. The second argument (condition as function) is a function that specifies the criteria for removing rows. The function takes a single argument (a row from the input table) and returns a boolean value that determines whether the row should be removed or not.

The Table.RemoveMatchingRows function works by iterating through each row of the input table and evaluating the condition function for that row. If the condition function returns true, the row is removed from the table. If the condition function returns false, the row is kept in the table.

Customizing the Table.RemoveMatchingRows Function

The Table.RemoveMatchingRows function can be customized to fit specific data cleaning needs by creating a custom condition function. This function can be written in M code and can be as simple or as complex as needed.

For example, suppose we have a table of customer data that includes a column for customer age. We want to remove all rows where the customer age is less than 18. To do this, we can create a custom condition function that checks the value of the customer age column for each row and returns true if the value is less than 18, and false if the value is greater than or equal to 18.

The code for this custom condition function would look like this:

We can then pass this function as the second argument to the Table.RemoveMatchingRows function to remove all rows where the customer age is less than 18.

Using the Table.RemoveMatchingRows Function in Practice

To use the Table.RemoveMatchingRows function in practice, we first need to open the Power Query Editor in Excel. We can then select the table we want to clean and click on the “Remove Rows” dropdown menu in the “Home” tab.

From this dropdown menu, we can select “Remove Matching Rows” to open the “Remove Matching Rows” dialog box. In this dialog box, we can specify the condition function that will be used to remove rows from the table.

For example, if we want to remove all rows where the customer age is less than 18, we can enter the following M code in the “Custom” tab of the “Remove Matching Rows” dialog box:

We can then click on the “OK” button to remove all rows where the customer age is less than 18.

The Table.RemoveMatchingRows function is a powerful tool for cleaning and transforming data in Power Query. By understanding the M code behind this function, we can customize it to fit specific data cleaning needs and remove only the rows that meet certain criteria. With the ability to write custom condition functions, the Table.RemoveMatchingRows function is a versatile tool that can be used to prepare data for analysis in Excel or other tools.

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

Upcoming Courses

Contact Us