Forum Discussion
PowerQueryEditor, Delete rows where a certain column is null for all rows per ID
Hi All,
After trying for several hours and searched on this forum I can't seem to find a good solution to my problem:
For a certain data set I would like to use to Power Query Editor to add an extra step in where I remove certain rows.
More in depth:
I would like to remove the rows that have a NULL value in the column TruckID for all the rows of a given OrderID. For instance in the table below all the yellow rows should be removed:
The rows with OrderID = 1 should all stay in the dataset because there is a truckid filled for one of the rows where OrderID = 1.
Any help would be greatly appreciated, thanks!
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYkMDpVgdCDcJiMEcI6gcnINdxhhmhCmcC1JoaATmmiDrM4HriwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrderID = _t, Type = _t, TruckId = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderID", Int64.Type}, {"Type", type text}, {"TruckId", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"OrderID"}, {{"Count", each _, type table [OrderID=nullable number, Type=nullable text, TruckId=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Remove", each if List.IsEmpty(List.RemoveNulls([Count][TruckId])) then "Y" else "N"), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Remove] = "N")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Remove"}), #"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Type", "TruckId"}, {"Type", "TruckId"}) in #"Expanded Count"
2 Replies
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYkMDpVgdCDcJiMEcI6gcnINdxhhmhCmcC1JoaATmmiDrM4HriwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrderID = _t, Type = _t, TruckId = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderID", Int64.Type}, {"Type", type text}, {"TruckId", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"OrderID"}, {{"Count", each _, type table [OrderID=nullable number, Type=nullable text, TruckId=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Remove", each if List.IsEmpty(List.RemoveNulls([Count][TruckId])) then "Y" else "N"), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Remove] = "N")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Remove"}), #"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Type", "TruckId"}, {"Type", "TruckId"}) in #"Expanded Count"