Forum Discussion
debojyoty
2 years agoFrequent Visitor
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/...
- 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:
- 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,
ThxAlot
2 years agoSuper User
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,