Forum Discussion

RafaelKnuth's avatar
RafaelKnuth
Advocate I
8 years ago
Solved

M Power Query: Removing entire rows based on condition

I want to remove entire rows IF both horizontally adjacent cells in the last two columns are empty (hence rows 4, 6, 7, 9 should be removed). How can I do this? Not sure by the way whether I should do this in M or DAX. Any help appreciated. Thanks!

 

Need Help
Show and Tell
Tips and Tricks
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    HI RafaelKnuth

     

    With Power Query...

     

    You can add a Custom Column....>>>>then filter out the Null values in that column and then delete this Custom Column

     

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi RafaelKnuth

     

    Try this Calculated Table

    From the Modelling Tab>>>NEW TABLE

    (Replace TableName and Column names with actual names of your Table and Columns)

     

    New Table =
FILTER (
    ADDCOLUMNS (
        TableName,
        "Remove Rows?", IF (
            ISBLANK ( TableName[Last Column] ) && ISBLANK ( TableName[2ndLast Column] ),
            "Yes",
            "No"
        )
    ),
    [Remove Rows?] = "No"
)
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      HI RafaelKnuth

       

      With Power Query...

       

      You can add a Custom Column....>>>>then filter out the Null values in that column and then delete this Custom Column

       

      • RafaelKnuth's avatar
        RafaelKnuth
        Advocate I
        8 years ago

        That works nicely in Excel Power Query. Thank you!

    • RafaelKnuth's avatar
      RafaelKnuth
      Advocate I
      8 years ago

      Thanks, awesome! Any idea how to do that in the Excel source file? (it's not possible to create new tables in Excel Power Query ... in contrast to Power BI).