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



## countryfan_nt (Sep 28, 2020)

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 (Sep 28, 2020)

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 (Sep 28, 2020)

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 (Sep 28, 2020)

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


----------



## shaowu459 (Sep 28, 2020)

Another option:

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


----------



## shaowu459 (Sep 28, 2020)

Select all columns and then：


----------



## countryfan_nt (Sep 28, 2020)

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 (Sep 28, 2020)

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 (Sep 28, 2020)

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


----------



## sandy666 (Sep 28, 2020)

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


----------



## countryfan_nt (Sep 28, 2020)

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 (Sep 28, 2020)

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 (Sep 28, 2020)

countryfan_nt said:


> 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 (Sep 28, 2020)

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


----------



## shaowu459 (Sep 28, 2020)

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 (Sep 28, 2020)

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 (Sep 28, 2020)

table.ColumNames should be *T*able.ColumNames

Power Query is case sensitive


----------



## sandy666 (Sep 28, 2020)

countryfan_nt said:


> 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 (Sep 28, 2020)

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


----------



## countryfan_nt (Sep 29, 2020)

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


----------



## sandy666 (Sep 29, 2020)

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_


----------



## countryfan_nt (Sep 28, 2020)

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


----------



## shaowu459 (Sep 29, 2020)

```
= Table.SelectRows(Source,each not ([ProjectAlias] = null))
```

This will remove entire rows of the table if [ProjectAlias] is null.


----------



## sandy666 (Sep 29, 2020)

as I said before (post#20) you can


sandy666 said:


> or just filter ProjectAlias column by _null_


anyway you need to know what and where features are in the PQ editor and how to use them
I suggest to read








						Power Query documentation - Power Query
					

Power Query is used to filter, combine, and mash-up data from one or more supported data sources.



					docs.microsoft.com
				




have a nice day


----------



## countryfan_nt (Sep 29, 2020)

you guys are truly awesome!!!


----------



## sandy666 (Sep 29, 2020)

You are welcome and thanks for the feedback


----------

