Help with grouping columns

[debojyoty]

I have a table as shown below and want it to be transformed to

Input

EID	StartDate	MGRID
100884	3/1/2018	101516
100884	8/1/2022	76728
10077	6/12/2000	10080
102083	4/1/2018	82224
102083	5/2/2023	82224
102085	5/7/2018	15384
102086	5/7/2018	15384
102558	6/11/2018	76728
102558	12/11/2023	101516
102558	9/6/2022	24976

Output

EID	StartDate	MGRID
100884	8/1/2022	76728
10077	6/12/2000	10080
102083	5/2/2023	82224
102085	5/7/2018	15384
102086	5/7/2018	15384
102558	12/11/2023	101516

For each employee, I only want to keep the latest start date.

I have seen several similar posts but just can't make this work. Can someone please help. I want to do this in Power Query Editor /  M Query in Power BI 

Regards,

-Debo

[audreygerred · Accepted solution]

In Power Query, sort the date column in descending order, add an index column, then remove duplicates on Employee ID. Only the most recent item for each EID will stay.

Here is an example I did:

This is when I am on the sorted rows step:

This is when I am on the remove duplicate step:

[ThxAlot · Accepted solution]

let
    #"Grouped Table" = Table.Group(DATA, "EID", {"grp", each Table.Sort(_, {"StartDate", Order.Descending}){0}}),
    #"Expanded grp" = Table.ExpandRecordColumn(#"Grouped Table", "grp", {"StartDate", "MGRID"}, {"StartDate", "MGRID"})
in
    #"Expanded grp"

Calculated table is way more concise and elegant,

[audreygerred · Accepted solution]

In Power Query, sort the date column in descending order, add an index column, then remove duplicates on Employee ID. Only the most recent item for each EID will stay.

Here is an example I did:

This is when I am on the sorted rows step:

This is when I am on the remove duplicate step: