remove rows where not all columns are blank

[Anonymous]

I would like to remove rows in my dataset where all columns accept the 1st one is empty.

I cant us the remove blank rows button as the 1st column always has a code in it.

I also cant remove them manually as it might not always be the same rows that are blank.

Any ideas

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

we can use the selectrows function in the power query editor to meet your requirement:

RemoveAllBlank = Table.SelectRows(#"Changed Type",each ([A] <> "" and [A] <> null) 
                                     or ([B] <> "" and [B] <> null)
                                     or ([C] <> "" and [C] <> null)
                                     or ([D] <> "" and [D] <> null)
                                     or ([E] <> "" and [E] <> null)
                                     or ([F] <> "" and [F] <> null)
                                     or ([G] <> "" and [G] <> null))

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYIjFwjlBqJidaKVjIAMR2QFQOQKkzRGk4AikJQJbilTCNsJm5wZnOuMLOcOljTHbaYFDqlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, A = _t, B = _t, C = _t, D = _t, E = _t, F = _t, G = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", type text}, {"F", type text}, {"G", type text}}),
    RemoveAllBlank = Table.SelectRows(#"Changed Type",each ([A] <> "" and [A] <> null) 
                                     or ([B] <> "" and [B] <> null)
                                     or ([C] <> "" and [C] <> null)
                                     or ([D] <> "" and [D] <> null)
                                     or ([E] <> "" and [E] <> null)
                                     or ([F] <> "" and [F] <> null)
                                     or ([G] <> "" and [G] <> null))
in
    RemoveAllBlank

change the [A] to [G] to your column names.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lid-msft

This seems like the solution to me but adding it to my code gives me a token error, any ideas?

[v-lid-msft]

Hi @Anonymous ,

Please add a comma at the end of step #"Removed Columns1", and also do not forget the change the #"Change Type" as the #"Removed Columns1" in the new step.

Please black the queries in your screenshot which may contain confidential information.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

hi @v-lid-msft 

Sorry to be a pain.

Im now getting another error lol, i believe i followed your instructions lol.

[v-lid-msft]

Hi @Anonymous ,

We need to add a right token at the end of last step, also please change the step names according to the following screenshot.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lid-msft 

Sorry i forgot to add the )

Now sorted, thanks

[Anonymous]

Hi @v-lid-msft 

Done that but still have the error

[v-lid-msft]

Hi @Anonymous ,

Sorry for my mistake in previous reply,  we need to add a right parentheses in the place of arrow of following picture.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdbuchanan71]

@Anonymous 

Can you add a temp column that combines together all the columns you are checking then remove blanks based on the combined column?  That should remove only the rows where all the fields are blank.  Then you can delete that helper column.