Automated time intervals between StartTime and EndTime in Power Query

[StephenK]

Hello all,

Lets say I have a table:

Date	Provider	StartTime	EndTime
2/26/2020	Provider A	8:00 AM	4:00 PM
2/26/2020	Provider B	9:00 AM	5:00 PM
2/26/2020	Provider C	7:45 AM	3:30 PM
2/26/2020	Provider D	10:00 AM	7:00 PM
2/26/2020	Provider E	8:00 AM	5:00 PM

I want to use M to create a new column that automatically fills in time values at preset intervals between the StartTime field and EndTime field.

Example:

Date	Provider	SlotTime	StartTime	EndTime
2/26/2020	Provider A	8:00 AM	8:00 AM	4:00 PM
2/26/2020	Provider A	8:15 AM	8:00 AM	4:00 PM
2/26/2020	Provider A	8:30 AM	8:00 AM	4:00 PM
2/26/2020	Provider A	8:45 AM	8:00 AM	4:00 PM

Ideally this solution could accomodate conditions that change the interval depending on the Provider. For example, Provider A intervals at 15 minutes, Provider B at 10 minutes, etc.

The current way I am doing this very clunky and does not allow for conditionally changing the intervals. 

I basically have a whole bunch of functions like the following that increment off of each other:

= Table.AddColumn(#"Filtered Rows", "Time1", each if ([StartTime]+#duration(0,0,15,0)) < [EndTime] then ([StartTime]+#duration(0,0,15,0)) else null)

= Table.AddColumn(Custom1, "Time2", each if [Time1] = null then null else if ([Time1]+#duration(0,0,15,0)) < [EndTime] then ([Time1]+#duration(0,0,15,0)) else null)

= Table.AddColumn(#"Added Custom", "Time3", each if [Time2] = null then null else if ([Time2]+#duration(0,0,15,0)) < [EndTime] then ([Time2]+#duration(0,0,15,0)) else null)

I then unpivot all the columns to get my time values into rows incremented by 15 minute intervals.

Does anyone have any ideas on how this could be done in a more efficient way where the interval can be changed based on the value of another field?

[Icey · Accepted solution]

Hi @StephenK ,

Try this:

1. Add a custom column.

= if [Provider] = "Provider A" then List.Times([StartTime], Duration.TotalMinutes([EndTime]-[StartTime])/15+1,#duration(0,0,15,0)) 
else if[Provider] = "Provider B" then List.Times([StartTime], Duration.TotalMinutes([EndTime]-[StartTime])/10+1,#duration(0,0,10,0)) 
else if[Provider] = "Provider C" then List.Times([StartTime], Duration.TotalMinutes([EndTime]-[StartTime])/15+1,#duration(0,0,15,0)) 
else if[Provider] = "Provider D" then List.Times([StartTime], Duration.TotalMinutes([EndTime]-[StartTime])/10+1,#duration(0,0,10,0))
else if[Provider] = "Provider E" then List.Times([StartTime], Duration.TotalMinutes([EndTime]-[StartTime])/10+1,#duration(0,0,10,0))  
else null

2. Expand to new rows.

Then, you can get this:

For more details, please check the attached PBIX file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StephenK]

@Icey This is great! Thank you so much. I'm gonna throw a curveball now--so with my current clunky formula, it stops the interval at the last 15 minutes before the EndTime so that the EndTime does not get counted in the interval. Example:

Date	Provider	SlotTime	StartTime	EndTime
2/27/2020	Provider A	8:00 AM	8:00 AM	8:45 AM
2/27/2020	Provider A	8:15 AM	8:00 AM	8:45 AM
2/27/2020	Provider A	8:30 AM	8:00 AM	8:45 AM

The idea is that 8:30 is the last 15 minute slot and the schedule closes at 8:45. The new formula is counting the 8:45 slot as well.

Date	Provider	SlotTime	StartTime	EndTime
2/27/2020	Provider A	8:00 AM	8:00 AM	8:45 AM
2/27/2020	Provider A	8:15 AM	8:00 AM	8:45 AM
2/27/2020	Provider A	8:30 AM	8:00 AM	8:45 AM
2/27/2020	Provider A	8:45 AM	8:00 AM	8:45 AM

How would you modify the formula to stop one interval before the EndTime?

[Icey]

Hi @StephenK ,

In the original formula, there is a "+1", just delete it.

Duration.TotalMinutes (...) / 15 + 1

So, try this:

= if [Provider] = "Provider A" then List.Times([StartTime], Duration.TotalMinutes([EndTime]-[StartTime])/15,#duration(0,0,15,0)) 
else if[Provider] = "Provider B" then List.Times([StartTime], Duration.TotalMinutes([EndTime]-[StartTime])/10,#duration(0,0,10,0)) 
else if[Provider] = "Provider C" then List.Times([StartTime], Duration.TotalMinutes([EndTime]-[StartTime])/15,#duration(0,0,15,0)) 
else if[Provider] = "Provider D" then List.Times([StartTime], Duration.TotalMinutes([EndTime]-[StartTime])/10,#duration(0,0,10,0))
else if[Provider] = "Provider E" then List.Times([StartTime], Duration.TotalMinutes([EndTime]-[StartTime])/10,#duration(0,0,10,0))  
else null

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StephenK]

Perfect! Thanks so much @Icey! Was able to finally close some gaps on a major report thanks to your help.

[Greg_Deckler]

I'm guessing @ImkeF might.