Splitting event duration rows spanning multiple days

[AlperN]

Hello there,

I need help transforming my event table into multiple rows based on "work day" start time. The "work day" starts at 08:00:00. Simply, an event record starting in work day 1 and ending in work day 3 must split into 3 rows (equal to day span).

Here is a look to what I want to achieve:

My data looks like this:

Id	Start Time	End Time
1	2020-01-05 15:20:00	2020-01-05 22:45:00
2	2020-01-11 10:20:00	2020-01-12 11:30:00
3	2020-01-14 16:55:00	2020-01-17 09:30:00

What I want to achieve:

Id	Start Time	End Time
1	2020-01-05 15:20:00	2020-01-05 22:45:00
2	2020-01-11 10:20:00	2020-01-12 08:00:00
2	2020-01-12 08:00:00	2020-01-12 11:30:00
3	2020-01-14 16:55:00	2020-01-15 08:00:00
3	2020-01-15 08:00:00	2020-01-16 08:00:00
3	2020-01-16 08:00:00	2020-01-17 08:00:00
3	2020-01-17 08:00:00	2020-01-17 09:30:00

I found a solution to a similar problem here.

But in that one, the reference time is 00:00:00 not 08:00:00. I'm not very familiar with Power Query so I could not modify it to my needs. I'd appreciate any help.

Thank you!

[Jimmy801]

Hello @AlperN 

this requires quite a lot of steps. Maybe there are simpler ways to do it. Thought it might be easier, started out and had quite some difficulties, but can present you a solution. This is the code

let
	Source = #table
	(
		{"Id","Start Time","End Time"},
		{
			{"1","43835,6388888889","43835,9479166667"},	{"2","43841,4305555556","43842,4791666667"},	{"3","43844,7048611111","43847,3958333333"}
		}
	),
    ToDateTime = Table.TransformColumns
    (
        Source,
        {
            {
                "Start Time",
                each DateTime.From(Number.From(_)),
                type datetime
            },
            {
                "End Time",
                each DateTime.From(Number.From(_)),
                type datetime
            }
        }
    ),
    AddColumn = Table.AddColumn
    (
        ToDateTime,
        "ListDates",
        (add)=> 
        List.Dates(Date.From(add[Start Time]), Duration.TotalDays(Date.From(add[End Time]) - Date.From(add[Start Time]))+1,#duration(1,0,0,0)),
        type date
    ),
    CreateNew = Table.AddColumn
    (
        AddColumn,
        "New Time",
        (add)=>
        List.Zip
        (
            {
                List.Transform(add[ListDates],(chlist) as datetime => if List.PositionOf(add[ListDates],chlist)=0 then DateTime.From(chlist) +  #duration(0,Time.Hour( add[Start Time]),Time.Minute(add[Start Time]), Time.Second(add[Start Time])) else DateTime.From(chlist) + #duration(0,8,0,0)),
                List.Transform(add[ListDates],(chlist) as datetime => if List.PositionOf(add[ListDates],chlist)=List.Count(add[ListDates])-1 then DateTime.From(chlist) +  #duration(0,Time.Hour( add[End Time]),Time.Minute(add[End Time]), Time.Second(add[End Time])) else DateTime.From(chlist) + #duration(1,8,0,0))
            }
        )


    ),
    ExpandNewTime = Table.ExpandListColumn(CreateNew, "New Time"),
    Transform = Table.TransformColumns
    (
        ExpandNewTime,
        {
            {
                "New Time",
                each Text.Combine(List.Transform(_, Text.From), ";"), type text
            }
        }


    ),
    SplitColumn = Table.SplitColumn(Transform, "New Time", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"New Time.1", "New Time.2"}),
    DeleteColumns = Table.RemoveColumns(SplitColumn,{"Start Time", "End Time", "ListDates"}),
    ChangeType = Table.TransformColumnTypes(DeleteColumns,{{"New Time.1", type datetime}, {"New Time.2", type datetime}}),
    RenameColumns = Table.RenameColumns(ChangeType,{{"New Time.1", "Start Time"}, {"New Time.2", "End Time"}})

in
    RenameColumns

The first two steps represent your data source, they have to replaced by your current Query/table.

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous · Accepted solution]

Hi @AlperN  & @Jimmy801 ,

my version of the code:

This is a function that takes Start and End date as parameters and split it based on the third (optional) parameter, which directs the time offset to the start of the day (in the current example it is 8 hours = #duraiton(0,8,0,0)):

(pStartTime as datetime, pEndTime as datetime, optional pOffset as duration)=>
let
    Offset = if pOffset = null then #duration(0,0,0,0) else pOffset,
    mStartDateTime = pStartTime,
    mEndDateTime = pEndTime,
    
    Min = (date1 as datetime, date2 as datetime)=> if date1 < date2 then date1 else date2,
    Max = (date1 as datetime, date2 as datetime)=> if date1 > date2 then date1 else date2,

    mModifiedStartDateTime = mStartDateTime - Offset,
    mModifiedEndDateTime = DateTime.From(mEndDateTime) - Offset,
    mDaysList = {Number.From(Date.From(mModifiedStartDateTime)) .. Number.From(Date.From(mModifiedEndDateTime))},
    
    MakeList = Table.FromRecords(List.Accumulate(mDaysList, {}, (s, a)=> s & {[Start Date = Max(DateTime.From(a), mModifiedStartDateTime)+Offset , End Date = Min(DateTime.From(a+1), mModifiedEndDateTime)+Offset]}))
in
    MakeList

This is how it is used in the current scenario:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZctBCsAgDETRq4SsFWZG09JcRbz/NUoXgm23//PGcHpxQahgRRgjhQTeVcoeT51luLZHGvETlJHZsETbXzceGfERp+FaYt4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, #"Start Time" = _t, #"End Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Start Time", type datetime}, {"End Time", type datetime}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fSplitDates([Start Time],[End Time], #duration(0,8,0,0))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Start Date", "End Date"}, {"Custom.Start Date", "Custom.End Date"})

in
    #"Expanded Custom"

Kind regards,

JB

[AlperN]

Thank you all for taking the time to help solve my problem!

@Anonymous  I marked your post as solution because of that function which can be applied in multiple scenarios.

[Anonymous]

Hi @AlperN ,

will this work if you deduct 8 hours from your start & end datetime (e.g. [StartTime] - #duration(0,8,0,0)) , apply the algorithm that you have found and add 8 hours back to the result?

Kind regards,

JB