# Power Query 2016: return the latest date in a subset



## SOQLee (Mar 9, 2018)

I have tracking data that contains many dates for the same person.  How can records be cleaned up so that it shows only the latest date for each person.  Here is an example of the data:


*Date**Personnel**Sold*2017-11-11Bill872017-11-11Susan732017-11-11Susan-52017-11-09Alexis262017-11-12Alexis02017-11-12Alexis752017-11-08Kate192017-11-07Bill332017-11-07Bill-152017-11-13Susan252017-11-14Kate822017-11-13Kate-7

<tbody>

</tbody>


----------



## ParamRay (Mar 9, 2018)

1) I've assumed your data source is an Excel table in the current workbook called "Tracking".
2) I've assumed your columns are called "Date", "Personnel" and "Sold", as shown in your example.


```
let
    Source = Excel.CurrentWorkbook(){[Name="Tracking"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Personnel"}, {{"All Rows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Last Record", each Table.Max([All Rows], "Date")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Last Date", each Record.Field([Last Record], "Date")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Last Sales", each Record.Field([Last Record], "Sold")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Personnel", "Last Date", "Last Sales"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Personnel", type text}, {"Last Date", type date}, {"Last Sales", type number}})
in
    #"Changed Type"
```


----------



## SOQLee (Mar 9, 2018)

Excellent!  Thanks for providing the code.


----------

