Forum Discussion

oscargushiken's avatar
oscargushiken
Frequent Visitor
4 years ago
Solved

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

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.

DATEEMPLOYEE_IDJOB_CODE
2/2/2021867530929030003
2/1/2021867530929030003
5/4/2020867530929030002
5/3/2020867530929030002
4/1/2020867530929030002
11/15/2019867530929020002
7/6/2018867530929020003
9/5/2016867530929020003
3/21/2013867530929020003
8/14/2010867530929020002
5/11/2005867530929020002
3/25/2002867530929010002
3/24/2002867530929010002
2/1/2001867530929010001

 

After removing duplicates, the table should look like this:

DATEEMPLOYEE_IDJOB_CODE
2/1/2021867530929030003
11/15/2019867530929020002
3/21/2013867530929020003
5/11/2005867530929020002
3/24/2002867530929010002
2/1/2001867530929010001

 

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

 

Cheers!

Need Help
Tips and Tricks
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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.