Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Power Query - Column with latest status per ID

Hi,  I have the first three columns of the following table available:  Order ID Status Order Flag (needs to be created) 100 1   100 2   100 3 1 200 1   200 2 1 ...
  • dufoq3's avatar
    2 years ago

    Create Blank Query and replace whole code with this one.

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMlSK1YGxjZDYxmC2EZIaI5iaWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order ID" = _t, #"Status Order" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order ID", Int64.Type}, {"Status Order", Int64.Type}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Order ID"}, {{"Max Status Order", each List.Max([Status Order]), type nullable number}, {"All", each _, type table [Order ID=nullable number, Status Order=nullable number]}}),
        #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Status Order"}, {"Status Order"}),
        Ad_OrderFlag = Table.AddColumn(#"Expanded All", "Order Flag", each if [Status Order] = [Max Status Order] then 1 else null, Int64.Type),
        #"Removed Columns" = Table.RemoveColumns(Ad_OrderFlag,{"Max Status Order"})
    in
        #"Removed Columns"