Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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!
Solved! Go to 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
Thanks, this is exactly what I looking for!
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
64 | |
52 | |
49 |
User | Count |
---|---|
217 | |
88 | |
76 | |
67 | |
60 |