Forum Discussion

internwithquest's avatar
internwithquest
Frequent Visitor
2 years ago
Solved

Filter a row based on a hierarchy

HI

 

I have a dataset containing ID values for sales in one column and the status of the sale in the second. This status column has the values 1, 2 or 3 based on if a product has been ordered, 1, delivered, 2, or payment has been recieved 3. I only want to keep the rows higest in the heirarchy for each sale ID, that is if a product has been delivered, i only want to keep the row where the status = 2 and delete the row where status = 1. 

 

Is there any way to do this in Power Query?

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    2 years ago

    See the sample code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzALCcgywTOMoazTMEsZ7gOF7iYC0QsFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales ID" = _t, #"Sales Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales ID", type text}, {"Sales Status", Int64.Type}}),
    #"Grouped Rows" = Table.FromRecords(Table.Group(#"Changed Type", {"Sales ID"}, {{"All", each Table.Max(_,"Sales Status")}})[All])
in
    #"Grouped Rows"

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    2 years ago

    See the sample code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzALCcgywTOMoazTMEsZ7gOF7iYC0QsFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales ID" = _t, #"Sales Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales ID", type text}, {"Sales Status", Int64.Type}}),
    #"Grouped Rows" = Table.FromRecords(Table.Group(#"Changed Type", {"Sales ID"}, {{"All", each Table.Max(_,"Sales Status")}})[All])
in
    #"Grouped Rows"