Forum Discussion

Vatz8's avatar
Vatz8
Helper I
3 years ago
Solved

Split Datetime range columns into multiple rows for each day.

Hi,

I want to split the datetime columns range into multiple rows as shown below.

 

ID|     punch_Start                     | punch_End
--------------------------------------------
A | 2019-03-04 23:18:00| 2019-03-04 23:21:00
--------------------------------------------
A | 2019-03-04 23:45:00| 2019-03-05 00:15:00
--------------------------------------------

 

Required Output-

ID|       punch_Start                   | punch_End
--------------------------------------------
A | 2019-03-04 23:18:00| 2019-03-04 23:21:00
--------------------------------------------
A | 2019-03-04 23:45:00| 2019-03-04 23:59:00
--------------------------------------------
A | 2019-03-04 23:59:00| 2019-03-05 00:00:00
--------------------------------------------
A | 2019-03-05 00:00:00| 2019-03-05 00:15:14

 

Please let me know how can I achieve this.

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Vatz8 
    Please refer to attached sample file with the proposed solution

    Table 2 = 
SELECTCOLUMNS ( 
    GENERATE ( 
        'Table',
        CALENDAR ( 'Table'[punch_Start], 'Table'[punch_End] )
    ),
    "ID", [ID],
    "Punch_Start", MAX ( [punch_Start], [Date] ),
    "Punch_End", MIN ( [punch_End], [Date] + TIME ( 23, 59, 0 ) )
)

1 Reply

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Vatz8 
    Please refer to attached sample file with the proposed solution

    Table 2 = 
SELECTCOLUMNS ( 
    GENERATE ( 
        'Table',
        CALENDAR ( 'Table'[punch_Start], 'Table'[punch_End] )
    ),
    "ID", [ID],
    "Punch_Start", MAX ( [punch_Start], [Date] ),
    "Punch_End", MIN ( [punch_End], [Date] + TIME ( 23, 59, 0 ) )
)