Forum Discussion
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 ...
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.
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.
