Group by Max date

[cottrera]

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/2015
Scott	153114	31/08/2023
Kuldip	153114	07/08/2015
Kuldip	153114	07/08/2015

I require some help to how I can group the Unit Reference to display the Max Completion Date and who the Surveyor was.
I would expect this result

Surveyor Name	Unit Reference	Date Completed
Chris	8088	28/09/2023
Scott	153114	31/08/2023

Please note I already have some M-Code steps being performed on this table.
thankyou
Richard

[Vijay_A_Verma]

Insert the below step where #"Changed Type" is my previous step

= Table.Group(#"Changed Type", {"Surveyor Name", "Unit Reference"}, {{"Date Completed", each List.Max([Date Completed]), type nullable date}})

[cottrera]

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

[Vijay_A_Verma · Accepted solution]

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"

[cottrera]

That works fine thank you

[Damian_CT_Nom]

Maybe not as elegant but after that I would sort the date column descending and delete duplicates by Unit Reference so you only get the max date for Unit Reference and it's surveyor.

[cottrera]

Hi 

thanks for the tip.  I have tried this however whenremoving duplicates it removes the wrong ones and does not leave the max date.
Richard

[Damian_CT_Nom]

That happens sometimes after sorting, power query doesn't reflect it. Add an index column after the sorting that would fix it.

[cottrera]

Thank you😀