How to use RemoveMatchingRows function in Excel Power Query to delete rows based on condition

Occasional Contributor

Hi,

How to code and use "RemoveMatchingRows" in Excel Power Query to remove complete rows from a table based on checking if cells' values in 2 or columns of this row are blanks, null, or empty, yet the 3rd column's cell contains data. 

I checked the Mircosoft learning site for example, but the given one is very straight forward and not providing much help. 

To illustrate what is needed, see below

 

Thamer_Tarabzouni_0-1667997593624.png

 

 

3 Replies

@Thamer_Tarabzouni 

You can try this suggestion with the attached file.

RemoveMatchingRows.JPG

Hi Quadruple_Pawn,
Thank you for your provided feedback. But its needs more work which I can handle.
However, is it required to create this additional column? Can't be done without it?

Thank you for your prompt support.

@Thamer_Tarabzouni 

This is the easiest suggestion i can give if you want to apply Power Query along with the Table.RemoveMatchingRows function. If you work with Office 365 or Excel 2021 you can alternatively try the LET and FILTER function. However this would be a possible solution without Power Query and without the Table.RemoveMatchingRows function.

=LET(result,FILTER(A2:C24,(A2:A24<>"")*((B2:B24="")+(C2:C24=""))),IF(result=0,"",result))

filter.JPG

If you work with Excel 2013 or Excel 2016 an alternative could be Advanced Filter.