Forum Discussion
Anonymous
2 years agoNot applicable
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 ...
- 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"
dufoq3
2 years agoCommunity Champion
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"