Forum Discussion

Thamer_Tarabzouni's avatar
Thamer_Tarabzouni
Copper Contributor
Nov 09, 2022

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

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

 

 

 

excel
power bi

3 Replies

    • Thamer_Tarabzouni's avatar
      Thamer_Tarabzouni
      Copper Contributor
      Nov 10, 2022
      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.
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Nov 10, 2022

        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))

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

         

         

Resources