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 to select last row of each item , something like below table, could you please assist for this ?
| ID | Item | select |
| 1 | A | no |
| 2 | A | no |
| 3 | A | yes |
| 4 | B | no |
| 5 | B | yes |
| 6 | C | no |
| 7 | C | no |
| 8 | C | yes |
| 9 | D | no |
| 10 | D | no |
| 11 | D | yes |
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
6 Replies
- dufoq3
Community Champion
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- dufoq3
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.
