PowerQueryEditor, Delete rows where a certain column is null for all rows per ID

[Anonymous]

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!

[Vijay_A_Verma · Accepted solution]

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]

Thanks a lot! This seems to work 🙂