Split Datetime range columns into multiple rows for each day.

[Vatz8]

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 · Accepted solution]

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 ) )
)