Forum Discussion
Solved
Group by Max date
Hi I have a resonably large table with the three fields shown below. Surveyor Name Unit Reference Date Completed Oliver 8088 08/09/2015 Chris 8088 28/09/2023 Rizwan 8088 08/09/2...
So, sorry. I misread the requirement.
See below code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s/JLEstUtJRsjCwsABSRgaGproGlroGFkqxOtFKzhlFmcXIskbGIFkjiGxQZlV5Yh5OzcHJ+SUlQGFDU2NDQxO4dgtdY0OwvHdpTkpmAaoCkAEWugbmRCiIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Surveyor Name" = _t, #"Unit Reference" = _t, #"Date Completed" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Surveyor Name", type text}, {"Unit Reference", Int64.Type}, {"Date Completed", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Unit Reference"}, {{"All", each Table.SelectRows(_, (x)=>x[Date Completed] = List.Max(_[Date Completed]))}})[[All]], #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Surveyor Name", "Unit Reference", "Date Completed"}, {"Surveyor Name", "Unit Reference", "Date Completed"}) in #"Expanded All"
Hi thank you for responding so quickly. However the output is stillthe same os before
Surveyor NameUnit ReferenceDate Completed
| Oliver Goldsmith | 8088 | 08/09/2015 |
| Rizwan Ahmed | 8088 | 08/09/2015 |
| Kuldip Jassal | 153114 | 07/08/2015 |
| Scott Williams | 153114 | 31/08/2023 |
| Chris Donczyk | 8088 | 28/09/2023 |
Richard
So, sorry. I misread the requirement.
See below code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s/JLEstUtJRsjCwsABSRgaGproGlroGFkqxOtFKzhlFmcXIskbGIFkjiGxQZlV5Yh5OzcHJ+SUlQGFDU2NDQxO4dgtdY0OwvHdpTkpmAaoCkAEWugbmRCiIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Surveyor Name" = _t, #"Unit Reference" = _t, #"Date Completed" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Surveyor Name", type text}, {"Unit Reference", Int64.Type}, {"Date Completed", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Unit Reference"}, {{"All", each Table.SelectRows(_, (x)=>x[Date Completed] = List.Max(_[Date Completed]))}})[[All]],
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Surveyor Name", "Unit Reference", "Date Completed"}, {"Surveyor Name", "Unit Reference", "Date Completed"})
in
#"Expanded All"
That works fine thank you