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
hello dufoq3 ,
I read the code, it seems that you added the index column then go row by row to check, that is correct with this instance, however If the ID column isn't order, does this code work correctly ?
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.