Splitting event duration rows spanning multiple days

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

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