cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bradpeschel
Regular Visitor

Separate Duration That Crosses Midnight Into New Items

Hello, I have [Event Start] & [Event End] Date/Time Fields, a [Duration] Field that is displayed in decimal hours, and a [Crew] Field. Some of the events do cross 12:00 AM and even exceed 24 Hours.

 

I need to ensure that the total duration summed per day per [Crew] does not exceed 24 hours. Is this possible? Will New Items be created and expand the data?

 

Thank you in advance for your assistance.

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

Hi @bradpeschel ,

 

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])))

 

17.jpg18.jpg

 

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)

 

19.jpg

 

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"

 

20.jpg 


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



Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-lid-msft
Community Support
Community Support

Hi @bradpeschel ,

 

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])))

 

17.jpg18.jpg

 

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)

 

19.jpg

 

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"

 

20.jpg 


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



Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I had to look twice at that List.Generate code, but that is actually pretty awesome what you posted there @v-lid-msft





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @bradpeschel ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JarroVGIT
Resident Rockstar
Resident Rockstar

Can you show show some sample data or a screenshot of your datamodel? I don't think I fully understand what you are trying to achieve. 

Migth be usefull to give some dummy examples in an Excel table 🙂

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors