Forum Discussion

StephenK's avatar
StephenK
Resolver I
6 years ago
Solved

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 ...
Tips and Tricks
  • Icey's avatar
    Icey
    6 years ago

    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's avatar
StephenK
Resolver I
6 years ago

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:

DateProviderSlotTimeStartTimeEndTime
2/27/2020Provider A8:00 AM8:00 AM8:45 AM
2/27/2020Provider A8:15 AM8:00 AM8:45 AM
2/27/2020Provider A8:30 AM8:00 AM8: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.

DateProviderSlotTimeStartTimeEndTime
2/27/2020Provider A8:00 AM8:00 AM8:45 AM
2/27/2020Provider A8:15 AM8:00 AM8:45 AM
2/27/2020Provider A8:30 AM8:00 AM8:45 AM
2/27/2020Provider A8:45 AM8:00 AM8:45 AM

 

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

Icey's avatar
Icey
Community Support
6 years ago

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's avatar
    StephenK
    Resolver I
    6 years ago

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