Forum Discussion
Solved
Get last status of item in Power Query (mcode)
Dear everyone, I have a query as below table ID Item 1 A 2 A 3 A 4 B 5 B 6 C 7 C 8 C 9 D 10 D 11 D I would like to add additional column ...
Hi thangdev,
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjKCs4zhLBMgywnMMoWzzIAsZzDLHM6ygLMsgSwXMMvQAME0hDBjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Item = _t]), GroupedRows = Table.Group(Source, {"Item"}, {{"All", each [ a = Table.AddIndexColumn(_, "IndexHelper",0,1), b = Table.AddColumn(a, "Select", (x)=> if x[IndexHelper] = List.Max(a[IndexHelper]) then "yes" else "no", type text), c = Table.RemoveColumns(b, {"IndexHelper"}) ][c], type table}}), Combined = Table.Combine(GroupedRows[All]) in Combined
Hi, yes this code groups by [Item] and add internal index column. Then it check whether current internal row [IndexHelper] equals max of current group [IndexHelper] and if yes then "yes" else "no".
It will check row by row with respecting of last step row order.
