Forum Discussion
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 ...
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"
Just used a DAX solution found here https://community.fabric.microsoft.com/t5/Desktop/Dynamic-Row-Numbers/m-p/52671 for something similar. This worked great for me.
If you need a PowerQuery solution you could look into group by and add a max column. Use advanced option with group by. Group by ID. First aggregation is All rows and select new column with MAX IDStatus.
In the next step expand the allrows column and you will have the max IDStatus attached to all the rows. The simply add a column with an if statement. If ID_status = Columnname for MAXIDSTATUS then 1 else 0
Hope this helps