Forum Discussion

debojyoty's avatar
debojyoty
Frequent Visitor
2 years ago
Solved

Help with grouping columns

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/...
  • audreygerred's avatar
    2 years ago

    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's avatar
    2 years ago
    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,