Forum Discussion
Anonymous
4 years agoNot applicable
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 a...
- 4 years ago
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"
Vijay_A_Verma
4 years agoMost Valuable Professional
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"
Anonymous
4 years agoNot applicable
Thanks a lot! This seems to work 🙂