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

 

[sandy666]

you can try

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    UOC = Table.UnpivotOtherColumns(Index, {"Index"}, "Attribute", "Value"),
    Pivot = Table.Pivot(UOC, List.Distinct(UOC[Attribute]), "Attribute", "Value"),
    RC = Table.RemoveColumns(Pivot,{"Index"})
in
    RC

with don;t aggregate option

btw. it will remove all full blank rows

 

[sandy666]

or this with columns sorted asc

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    UOC = Table.Unpivot(Index, Table.ColumnNames(Source), "Attribute", "Value"),
    Sort = Table.Sort(UOC,{{"Attribute", Order.Ascending}}),
    Pivot = Table.Pivot(Sort, List.Distinct(Sort[Attribute]), "Attribute", "Value"),
    RC = Table.RemoveColumns(Pivot,{"Index"})
in
    RC

 

[sandy666]

or just use
= Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

 

[shaowu459]

Another option:

= Table.RemoveMatchingRows(Source,{Record.FromList(List.Repeat({null},Table.ColumnCount(Source)),Table.ColumnNames(Source))})

 

[shaowu459]

Select all columns and then：

 

[countryfan_nt]

WOW! Thanks a lot! so all of the above will ONLY delete the rows 7, 11, 13, 14 and 15; the deleting of any should only happen if all the row is blank in all column.

 

[sandy666]

if there are only 7,11,13,14,15 are totally blank they will be removed
if there is more totally blank rows they will be removed also

 

[sandy666]

btw. post#6 is the same as post#4

 

[sandy666]

one more alternative
= Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {not null})))
it will select all rows with data instead removing null rows