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 d...
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

     

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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Zubar 

      Do you mean that if I load 100,000 row table to power BI

      if I want to delete from Row 5 till 100,000 , I can just write bSe on those script you share with me ?

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