Forum Discussion

Fabi_Rntmn's avatar
Fabi_Rntmn
Regular Visitor
4 years ago
Solved

Split Rows for Entries Greater Than 24 Hours

Hello Together, 

 

imagine I have the following table consisting of start times and end times where one entry is larger than 24 hours (here 48 hours):

 

How can I cut this entry in this case into 3 entries for each day such that I have:

1) 11.06.2022 06:00:00 - 11.06.2022 23:59:59

2) 12.06.2022 00:00:00 - 12.06.2022 23:59:59

3) 13.06.2022 00:00:00 - 11.06.2022 06:00:00

 

Thank you very much for your help, I really appreciate it!

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Fabi_Rntmn 
    This is a bet complex. Please refer to sample file with the solution https://we.tl/t-rgdc467UOW

    New Data = 
VAR T1 =
    GENERATE (
        Data,
        VAR StartTime = Data[StartTime]
        VAR EndTime = Data[EndTime]
        VAR StartDate = DATE ( YEAR ( StartTime ), MONTH ( StartTime ), DAY ( StartTime ) )
        VAR EndDate = DATE ( YEAR ( EndTime ), MONTH ( EndTime ), DAY ( EndTime ) )
        VAR HoursToAdd = TIME ( 23, 59, 59 )
        RETURN
            ADDCOLUMNS (
                CALENDAR ( StartTime, EndTime ),
                "@NewStartTime", IF ( [Date] = StartDate, StartTime, [Date] ),
                "@NewEndTime", IF ( [Date] = EndDate, EndTime, [Date] + HoursToAdd )
            )
    )
RETURN
    SELECTCOLUMNS (
        T1,
        "Start Time", [@NewStartTime],
        "End Time", [@NewEndTime],
        "Time Difference", 
            DIVIDE ( 
                DATEDIFF ( [@NewStartTime], [@NewEndTime], SECOND ), 
                3600 
            )
    )

      

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Fabi_Rntmn 
    This is a bet complex. Please refer to sample file with the solution https://we.tl/t-rgdc467UOW

    New Data = 
VAR T1 =
    GENERATE (
        Data,
        VAR StartTime = Data[StartTime]
        VAR EndTime = Data[EndTime]
        VAR StartDate = DATE ( YEAR ( StartTime ), MONTH ( StartTime ), DAY ( StartTime ) )
        VAR EndDate = DATE ( YEAR ( EndTime ), MONTH ( EndTime ), DAY ( EndTime ) )
        VAR HoursToAdd = TIME ( 23, 59, 59 )
        RETURN
            ADDCOLUMNS (
                CALENDAR ( StartTime, EndTime ),
                "@NewStartTime", IF ( [Date] = StartDate, StartTime, [Date] ),
                "@NewEndTime", IF ( [Date] = EndDate, EndTime, [Date] + HoursToAdd )
            )
    )
RETURN
    SELECTCOLUMNS (
        T1,
        "Start Time", [@NewStartTime],
        "End Time", [@NewEndTime],
        "Time Difference", 
            DIVIDE ( 
                DATEDIFF ( [@NewStartTime], [@NewEndTime], SECOND ), 
                3600 
            )
    )

      

    • Fabi_Rntmn's avatar
      Fabi_Rntmn
      Regular Visitor
      4 years ago

      Hi tamerj1
      Thank you so much for your quick help! This worked quite well 🙂