Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I'm trying to merge some rows of a table based on consecutive time intervals. My table looks something like this:
Start Date | End Date | Value |
01/01/2022 | 02/01/2022 | 10 |
02/01/2022 | 04/01/2022 | 10 |
04/01/2022 | 05/15/2022 | 10 |
05/15/2022 | 06/01/2022 | 20 |
06/01/2022 | 08/13/2022 | 20 |
08/13/2022 | 09/01/2022 | 20 |
09/01/2022 | 10/31/2022 | 20 |
10/31/2022 | 12/24/2022 | 10 |
12/24/2022 | 01/10/2023 | 10 |
01/10/2023 | 03/27/2023 | 10 |
I would like to merge the rows within each block of similar values in the "value" column, so that the result looks like:
Start Date | End Date | Value |
01/01/2022 | 05/15/2022 | 10 |
05/15/2022 | 10/31/2022 | 20 |
10/31/2022 | 03/27/2023 | 10 |
A simple
GROUPBY(table, [Value], "Start Date Merged", MINX(CURRENTGROUP(), [Start Date]), "End Date Merged", MAXX(CURRENTGROUP(), [End Date]))
doesn't work because the values in "Value" are not unique outside the blocks I want to merge.
Any help with this would be greatly appreciated, as I have been trying to wrap my head around this for quite some time.
Thanks!
@swandernoth , Refer if this can help , you case the diff is 0
Continuous streak: https://youtu.be/GdMcwvdwr0o
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
46 | |
28 | |
25 | |
18 | |
17 |