Forum Discussion
Solved
Advanced Power Query - Group by dataset including Start Date and End Date
HI,
I have below Source dataset
| Source data | ||||
| Employee ID | Function | Start Date | End Date | Days |
| A001 | PM | 2024-01-01 | 2024-01-31 | 31 |
| A001 | PM | 2024-02-01 | 2024-02-15 | 15 |
| A001 | Consultant | 2024-02-16 | 2024-02-29 | 14 |
| A001 | PM | 2024-03-01 | 2024-03-31 | 31 |
| A001 | Consultant | 2024-04-01 | 2024-04-30 | 30 |
| 121 |
And i was hoping to produce the below summary
| Target summary | ||||
| Employee ID | Function | Start Date | End Date | Days |
| A001 | PM | 2024-01-01 | 2024-02-15 | 46 |
| A001 | Consultant | 2024-02-16 | 2024-02-29 | 14 |
| A001 | PM | 2024-03-01 | 2024-03-31 | 31 |
| A001 | Consultant | 2024-04-01 | 2024-04-30 | 30 |
| 121 |
when using Group by function for Employee ID and Function with min for start date and max for end date. I got the below summary which is not correct.
| Group by Emplee ID and Function. - Incorrect Summary | ||||
| Employee ID | Function | Start Date | End Date | Days |
| A001 | PM | 2024-01-01 | 2024-03-31 | 91 |
| A001 | Consultant | 2024-02-16 | 2024-04-30 | 75 |
| Incorrect | 166 |
Does any expert have any good solution? Many thanks in advance.
2 Replies
Hi Nate, Many thanks for your prompt response. i works perfectly. Calvin
