Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
57 | |
33 | |
18 | |
18 | |
15 |
User | Count |
---|---|
94 | |
86 | |
39 | |
22 | |
20 |