PQ: Remove/Delete rows if specfic row of all columns are blank.

[countryfan_nt]

Hello friends, Hope all is safe & well!
Please help me with Power Query. I want to delete the entire rows: 7, 11, 13, 14 and 15; based on the conditions that all cells are empty in all existing columns.

Thank you very much in advance!

Please

 

[countryfan_nt]

seriously thanks a lot! OK one last hiccup; please help. I wish to delete all rows based on the column before the last "ProjectAlias". If it is Null, then delete all rows.
So sorry, but this is the icing on the cake! much appreciated!

 

[sandy666]

I wish to delete all rows based on the column before the last "ProjectAlias". If it is Null, then delete all rows.

this is not clear to me
[PatientID] = null
[ProjectAlias] = null
so?
or this is a wrong picture

 

[sandy666]

or you want remove (not delete) columns = null except ProjectAlias and next to right?

 

[shaowu459]

Is this what you want? The column name before " ProjectAlias " may vary?

The "ProjectAlias" column is the 7th column:

The "ProjectAlias" is the 6th column:

 

[countryfan_nt]

Sorry for the confusion; Basically, I want to Delete all rows if "ProjectAlias" =null.

When I used the below code, I got this message: "Expression.Error: The name 'table.ColumNames' wasn't recognized. Make sure it's spelled correctly."

= let a=table.ColumNames(Source),
        b=List.PositionOf(a,"ProjectAlias")-1,
        c=List.Select(Table.ToRows(Source),each not (_{b}=null))
in
#table(a,c)

 

[sandy666]

table.ColumNames should be Table.ColumNames

Power Query is case sensitive

 

[sandy666]

I want to Delete Remove all rows if "ProjectAlias" =null.

you can add Custom Column with [ProjectAlias]=null then filter this column by TRUE (stay with FALSE) and remove Custom Column

 

[sandy666]

= Table.RemoveColumns(Table.SelectRows(Table.AddColumn(previous_step, "Custom", each [ProjectAlias] = null), each ([Custom] = false)),{"Custom"})

 

[countryfan_nt]

Thank you very much! so Table.RemoveColumns will remove Rows that have null in ProjectAlias? is there a thing called Table.RemoveRows ?
Thanks a lot!

 

[sandy666]

if you add this line to the end of your code in Advanced Editor with step name, eg. Filter you will see how it works
Filter = Table.RemoveColumns(Table.SelectRows(Table.AddColumn(previous_step, "Custom", each [ProjectAlias] = null), each ([Custom] = false)),{"Custom"})
example

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Filter = Table.RemoveColumns(Table.SelectRows(Table.AddColumn(Source, "Custom", each [ProjectAlias]=null), each ([Custom] = false)),{"Custom"})
//in Filter line  Source is a name of previous step
in
    Filter

in short
Table.RemoveColumns will remove column you just created to remove nulls in ProjectAlias column

post your code from Advanced Editor here, use code tags [code]your code here[/code]

or just filter ProjectAlias column by null