Forum Discussion
remove rows where not all columns are blank
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
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 RemoveAllBlankchange 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.
9 Replies
- jdbuchanan71
Super User
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.
v-lid-msftCommunity Support
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 RemoveAllBlankchange 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.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.
