Forum Discussion
Summarising Tables
Hi,
I have a simple table which I would like to summarise by only returning the row with the latest (max) ID for each REF.
Any help with this would be appreciated.
Current:
| REF | ID | ID_DESC | DATE |
| 130339/02 | 598 | AAA | 23/02/2021 |
| 130339/02 | 660 | BBB | 22/02/2021 |
| 130411/02 | 529 | AAA | 24/02/2021 |
| 130411/02 | 547 | BBB | 24/02/2021 |
| 130411/02 | 570 | CCC | 23/02/2021 |
| 130411/02 | 571 | DDD | 24/02/2021 |
| 130411/02 | 595 | EEE | 24/02/2021 |
| 130411/02 | 596 | FFF | 25/02/2021 |
| 130411/02 | 597 | GGG | 24/02/2021 |
| 130411/02 | 660 | HHH | 01/03/2021 |
| 130411/02 | 695 | III | 01/03/2021 |
| 131043/05 | 599 | AAA | 01/03/2021 |
| 131043/05 | 660 | BBB | 02/03/2021 |
| 131043/05 | 695 | CCC | 02/03/2021 |
| 131432/04 | 571 | AAA | 22/02/2021 |
| 131432/04 | 576 | BBB | 22/02/2021 |
| 131432/04 | 595 | CCC | 22/02/2021 |
| 131432/04 | 640 | DDD | 22/02/2021 |
| 131432/04 | 670 | EEE | 24/02/2021 |
| 131432/04 | 695 | FFF | 24/02/2021 |
Desired Output:
| REF | ID | ID_DESC | DATE |
| 130339/02 | 660 | BBB | 22/02/2021 |
| 130411/02 | 695 | III | 01/03/2021 |
| 131043/05 | 695 | CCC | 02/03/2021 |
| 131432/04 | 695 | FFF | 24/02/2021 |
Best wishes,
Jamie.
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZJLEoQgDETvwtoqAwQcln7BM1je/xoT0BqiI7jpRepVd0izbUJq0Nq1oEQjjPuQ9n1PqjTNWgVKir25YtYC6TAMEVN3DKU83ZTLbljGsMtuFayLoeM4Pu7GMEk6TdOLmzOk8zy/YZZ0WZaImQoWn+C9r7sddwshkALNdAFLu63r+oRJQHq8SaH5vBWMl0WLFbEUepz3H0NNI/yd9+z0Xj3HbPmHMIyFVjCLkDutYOmHFDplWAo9O2XY/gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [REF = _t, ID = _t, ID_DESC = _t, DATE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"REF", type text}, {"ID", Int64.Type}, {"ID_DESC", type text}, {"DATE", type text}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"DATE", type date}}, "en-GB"), #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"REF"}, {{"AllRows", each _, type table [REF=nullable text, ID=nullable number, ID_DESC=nullable text, DATE=nullable date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max([AllRows], "DATE")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}), #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"ID", "ID_DESC", "DATE"}, {"ID", "ID_DESC", "DATE"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"ID", Int64.Type}, {"ID_DESC", type text}, {"DATE", type date}}) in #"Changed Type1"Also see this article. How to Group By Maximum Value using Table.Max - Power Query (gorilla.bi)
Pat
3 Replies
aJamie , Try a new Table
filter(addcolumn(Table, "_max" , maxx(filter(Table, [REF] =earlier([REF])),[ID])), [ID] =_max)
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZJLEoQgDETvwtoqAwQcln7BM1je/xoT0BqiI7jpRepVd0izbUJq0Nq1oEQjjPuQ9n1PqjTNWgVKir25YtYC6TAMEVN3DKU83ZTLbljGsMtuFayLoeM4Pu7GMEk6TdOLmzOk8zy/YZZ0WZaImQoWn+C9r7sddwshkALNdAFLu63r+oRJQHq8SaH5vBWMl0WLFbEUepz3H0NNI/yd9+z0Xj3HbPmHMIyFVjCLkDutYOmHFDplWAo9O2XY/gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [REF = _t, ID = _t, ID_DESC = _t, DATE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"REF", type text}, {"ID", Int64.Type}, {"ID_DESC", type text}, {"DATE", type text}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"DATE", type date}}, "en-GB"), #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"REF"}, {{"AllRows", each _, type table [REF=nullable text, ID=nullable number, ID_DESC=nullable text, DATE=nullable date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max([AllRows], "DATE")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}), #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"ID", "ID_DESC", "DATE"}, {"ID", "ID_DESC", "DATE"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"ID", Int64.Type}, {"ID_DESC", type text}, {"DATE", type date}}) in #"Changed Type1"Also see this article. How to Group By Maximum Value using Table.Max - Power Query (gorilla.bi)
Pat
