Forum Discussion
Solved
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...
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
