Forum Discussion
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/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
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:
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,
2 Replies
- audreygerred
Super User
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
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,