M Power Query: Removing entire rows based on condition

[RafaelKnuth]

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!

[Zubair_Muhammad]

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 · Accepted solution]

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

[Paulyeo11]

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]

That works nicely in Excel Power Query. Thank you!

[RafaelKnuth]

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