Filter a row based on a hierarchy

[internwithquest]

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?

[Vijay_A_Verma · Accepted solution]

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"