cancel
Showing results for
Did you mean: 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?

1 ACCEPTED SOLUTION  Community Support

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 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.
4 REPLIES 4  Community Support

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 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.  Resident Rockstar

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

Proud to be a Super User!  Community Support

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.  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! 🙂

Proud to be a Super User! Announcements #### The Power BI Community Show

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