I have 2 columns in Excel with times, I need to round one column down to the nearest 5 minutes and the other one up to the nearest 5 minutes.
For example:
8:24 would round down to 8:20
15:34 would round up to 15:35
In Excel I'd use these formulas:
=FLOOR(A1,"00:05")
=CEILING(A1,"00:05")
I can't find the equivalent in Power Query, the closest I get is Time.StartOfHour
, what I need is the equivalent of Time.StartOf5Minutes
and Time.EndOf5Minutes
!
I used custom functions for similar task
RoundDown = (Time as time) as time =>
let
Minutes = Time.Hour(Time) * 60 + Time.Minute(Time),
RoundedMinutes = Number.RoundDown(Minutes / 5) * 5,
Result = #time(0, 0, 0) + #duration(0, 0, RoundedMinutes, 0)
in
Result
and
RoundUp = (Time as time) as time =>
let
Minutes = Time.Hour(Time) * 60 + Time.Minute(Time),
RoundedMinutes = Number.RoundUp(Minutes / 5) * 5,
Result = #time(0, 0, 0) + #duration(0, 0, RoundedMinutes, 0)
in
Result
Example
let
Source = Table.FromRecords({
[Column1 = #time(1, 12, 0)]
}),
Down = Table.AddColumn(Source, "Down", each RoundDown([Column1]), type time),
Up = Table.AddColumn(Down, "Up", each RoundUp([Column1]), type time)
...
in
Up