Forum Discussion

StephenK's avatar
StephenK
Icon for Resolver I rankResolver 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.