Forum Discussion

Vatz8's avatar
Vatz8
Icon for Helper I rankHelper 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-0...
  • 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 ) )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity 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 ) )
)