Group Two Columns with Two Different Logic

[derickyy]

Hi,

Can you please help me with the following table manipulation? It would be best if it can be achieved through power query but dax query is also acceptable.

Basically, I have one table with persons' names and their starte_date and end_date values. I want to group the start_date and end_date values by their persons' names but when doing the grouping, I need the MIN value from start_date and MAX value from end_date so as to get a date range. Please refer to the tables below:

Raw data:

Person	Start Date	End Date
A	March-1-2021	July-1-2021
A	Jan-1-2021	March-1-2021
A	Aug-1-2020	Dec-1-2020
B	July-1-2021	Dec-1-2021
B	June-1-2021	July-1-2021
C	March-1-2021	August-1-2021
C	Jan-1-2021	March-1-2021
C	June-1-2020	August-1-2020
C	Jan-1-2020	June-1-2020

Output:

Person	Start Date	End Date
A	Aug-1-2020	July-1-2021
B	June-1-2021	Dec-1-2021
C	Jan-1-2020	August-1-2021

Please let me know if anyone has any thoughts on this. Thanks in advance!

[derickyy]

Thanks, this is exactly what I looking for!

[HotChilli · Accepted solution]

Use the 'Group By' in Power Query.

You can add 2 aggregations by selecting the 'Advanced' radio button.

So 'Group By' the Person and add the 2 aggregations.

I'll help if you get stuck