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, d...
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"
Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost 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"