Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
  • Vijay_A_Verma's avatar
    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"