excelpowerquerym

I need to find the closest 5 minutes of times using Power Query


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!


Solution

  • 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
    

    enter image description here