Forum Discussion
Automated time intervals between StartTime and EndTime in Power Query
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?
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 null2. 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.
5 Replies
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 null2. 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.
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?
Hi StephenK ,
In the original formula, there is a "+1", just delete it.
Duration.TotalMinutes (...) / 15 + 1So, 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 nullBest Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I'm guessing ImkeF might.
