Removing the matching rows or occurrence of specified rows from the table using Power query is very easy as Power Query provides in-built function named Table.RemoveMatchingRows. This is very useful when you do not want to load spcefied rows based on some column values.
Let’s see the syntax of function
Table.RemoveMatchingRows(table as table, rows as list, optional equationCriteria as any) as table
- table The table from which you want to remove rows.
- rows A list containing the criteria you want to define to remove the specific rows, you can specify the criteria such as Column name =Value. For example Country =”Germany”.
- equationcriteria
An optional value that specifies how to control comparison between the rows of the table.
Let’s see how to using this function to remove the occurrence of specified rows from the table.
As you can see here we have a table as shown below.
Now, assume that you want remove rows where country Germany.
Go to Power Query Editor, now we will add a new step in Power Query for that click on last created step that you can see under Applied Steps pane on the right side.
For us the last created step in Power Query is Changed Type, so right click on that step and select Insert Step After option.
Now, you will see a step is created with the name Custom1 and it opens a formula bar that allows you to write your M query code.
Let’s modify the code in formula as given below.
Also note that, here Changed Type is the last step in our case, it could be different in your case based on the transformations that you have done in Power Query so just modify your code accordingly by just replacing the last step name with Changed Type in below M query code.
If it is same for you then you can use same code no need to modify.
= Table.RemoveMatchingRows(#"Changed Type",{[Country ="Germany"]}, "Country")
Once you done with the above M Query code, you will see the all the rows belongs to the Country Germany has been removed from the table.
You can confirm the same by filtering the records for country Germany as shown below.
You can see there is no records for country Germany.
Now, assume that this time we want to remove the rows based on multiple column values.
Let’s add one more criteria that is Remove all rows from table where Country is Germany and Product is Paseo.
In this case, we can simply modify above M code as shown below.
= Table.RemoveMatchingRows(#"Changed Type",{[Country ="Germany", Product ="Paseo"]}, {"Country", "Product"})
Now, you can see it removes rows for matching criteria where Country is Germany and Product is Paseo as shown below.
Also Read..
