Eliminate duplicate values based on earliest date and filtered by another column

[oscargushiken]

Dear forum,

I am attempting to issolate the distinct JOB_CODE values by the earliest date it appears in a row for each EMPLOYEE_ID.  I have a data table that records employee events, such has changes in department and position and it does this by JOB_CODE.  But not every event marks a change in JOB_CODE.  By issolating the earliest known distinct JOB_CODE for each EMPLOYEE_ID, I hope to be able to report on their movements within the organization.

Below is sample data of what my table looks like.

DATE	EMPLOYEE_ID	JOB_CODE
2/2/2021	8675309	29030003
2/1/2021	8675309	29030003
5/4/2020	8675309	29030002
5/3/2020	8675309	29030002
4/1/2020	8675309	29030002
11/15/2019	8675309	29020002
7/6/2018	8675309	29020003
9/5/2016	8675309	29020003
3/21/2013	8675309	29020003
8/14/2010	8675309	29020002
5/11/2005	8675309	29020002
3/25/2002	8675309	29010002
3/24/2002	8675309	29010002
2/1/2001	8675309	29010001

After removing duplicates, the table should look like this:

DATE	EMPLOYEE_ID	JOB_CODE
2/1/2021	8675309	29030003
11/15/2019	8675309	29020002
3/21/2013	8675309	29020003
5/11/2005	8675309	29020002
3/24/2002	8675309	29010002
2/1/2001	8675309	29010001

My initial thought is to create a new table from the source table, but any assistance is appreciated.

Cheers!

[Ashish_Mathur · Accepted solution]

Hi,

See if this M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type datetime}, {"EMPLOYEE_ID", Int64.Type}, {"JOB_CODE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"EMPLOYEE_ID","JOB_CODE"}, {{"All", each Table.Max(_,"DATE")}}),
    #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"DATE"}, {"DATE"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded All",{{"DATE", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"EMPLOYEE_ID", Order.Ascending}, {"DATE", Order.Descending}})
in
    #"Sorted Rows"

Hope this helps.

[oscargushiken]

I am impressed, it worked!

Thank you so much.

[Ashish_Mathur]

Thank you.  You are welcome.