Separate Duration That Crosses Midnight Into New Items

Hi Anonymous ,

 

Firstly, we can Create a custom column as the new start time and expand it to spreate the event:

 

let d = Duration.Days([Event End] - [Event Start])
in
List.Generate(()=> [ x = 0 , y = [Event Start]],each [x] <= d, each [x =[x]+ 1,y = [y]+
 #duration(1,0,0,0)],each if[x]=0 then [y] else [y] - #duration(0,Time.Hour([y]),Time.Minute([y]),Time.Second([y])))

 

 

Then we can create another column as the new end time.

 

if[Event Start] = [New Event Start] 
then if Duration.TotalDays([Event End]-[Event Start])<1
    then [Event End]
    else #datetime(Date.Year([Event Start]),Date.Month([Event Start]),Date.Day([Event Start]),23,59,59)
else if Duration.TotalDays([Event End]-[New Event Start] )<1
    then [Event End]
    else
 #datetime(Date.Year([New Event Start]),Date.Month([New Event Start]),Date.Day([New Event Start]),23,59,59)

 

 

All the queries are here:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMLTUN9Q3VDAwtDIwACKFxFwUYXMUYaVYnWggSUifOZqwMVifMcn6TMD6TEh2pyFYnylBfUaY+mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Event Start" = _t, #"Event End" = _t, Crew = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Event Start", type datetime}, {"Event End", type datetime}, {"Crew", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "New Event Start", each let d = Duration.Days([Event End] - [Event Start])
in
List.Generate(()=> [ x = 0 , y = [Event Start]],each [x] <= d, each [x =[x]+ 1,y = [y]+
 #duration(1,0,0,0)],each if[x]=0 then [y] else [y] - #duration(0,Time.Hour([y]),Time.Minute([y]),Time.Second([y])))),
    #"Expanded New Event Start" = Table.ExpandListColumn(#"Added Custom", "New Event Start"),
    #"Added Custom1" = Table.AddColumn(#"Expanded New Event Start", "New Event End", each if[Event Start] = [New Event Start] 
then if Duration.TotalDays([Event End]-[Event Start])<1
    then [Event End]
    else #datetime(Date.Year([Event Start]),Date.Month([Event Start]),Date.Day([Event Start]),23,59,59)
else if Duration.TotalDays([Event End]-[New Event Start] )<1
    then [Event End]
    else
 #datetime(Date.Year([New Event Start]),Date.Month([New Event Start]),Date.Day([New Event Start]),23,59,59))
in
    #"Added Custom1"

 

 

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.

Best regards,