Power Query - Keeping Most recent records in change log columns

Question

I need to strip records to show just the most recent for a given person, and I'm trying to think of a method for doing this in a custom column so I can just keep the most recent records. This is essentially a a status change list, and I need to match the last change as a "current status" for merging with another query. Each date can be unique, and each person can have any from 1 to a dozen status changes. I've picked a selection below, Last Names have been removed to protect the innocent. For sake of the example, Each "name" has a unique identifier that I can use to prevent any overlap from similar names.

 AaronS 4/1/2015
 AaronS 10/16/2013
 AaronS 5/15/2013
 AdamS  2/27/2007
 AdamL  12/16/2004
 AdamL  11/17/2004
 AlanG  11/1/2007
 AlexanderJ 7/1/2016
 AlexanderJ 1/25/2016
 AlexanderJ 4/1/2015
 AlexanderJ 10/16/2013
 AlexanderJ 6/1/2013
 AlexanderJ 11/7/2011

My goal would be to return the most recent date for each individual "name" and nulls for the other rows. Then I can filter out nulls to return one row per name. I'm fairly new to power query and mostly adept with the UI, barely learning M Code. Any help will be most welcome.

Answer 1

GUI

1. Bring the "Name" and "Date" data into Power Query.
2. Group by "Name". In the Group By dialog select the operation All Rows. Name the new column "AllRows". Click OK.
3. Add a custom column and title it "LatestRow". Enter the formula below. Click OK. Note that the "Date" column is coming from the sub-table in the "AllRows" column.

= Table.Max([AllRows], "Date")

4. Click the expand button in the upper right corner of the "LatestRow" column. This will return the record associated with the latest date for each name.

Code

let
    Source            = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    GroupedRows       = Table.Group(Source, {"Name"}, {{"AllRows", each _, type table [Name=nullable text, Date=nullable datetime]}}),
    AddedCustomColumn = Table.AddColumn(GroupedRows, "LatestRow", each Table.Max([AllRows], "Date")),
    ExpandedLatestRow = Table.ExpandRecordColumn(AddedCustomColumn, "LatestRow", {"Date"}, {"LatestRow.Date"})
in
    ExpandedLatestRow