Splitting event duration rows spanning multiple days

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