Round DateTime With Power Query

When working with Power BI and datetime and datetimezone data types, the level of granularity presented with down to the second data types are usually not necessary for most use cases. Many times, you just change the data type to date and move on.

But sometimes you need something more specific than a date. I am working on a project now where the times need to be rounded to the nearest half hour. How do you do that?

Microsoft MVP Rick de Groot has a wonderful walkthrough on how to do this:

I followed the walkthrough and made my own function afterwards. Surely this has been done numerous times before, but it was nice to code it (with Rick’s help). The nice thing about the function is that you can set your own interval (15, 20, 30 minutes as examples) and it will round it for you.

Here is the code:

let
  fnRoundDateTime = (dt as datetime, interval as number) =>
    let
      Source   = Number.From(dt), 
      Minutes  = 24 * 60 / interval, 
      Rounded  = Number.Round((Source * Minutes), 0) / Minutes, 
      DateTime = DateTime.From(Rounded)
    in
      DateTime, 
  fnType = type function (dt as datetime, interval as number) as datetime
    meta [
      Documentation.Name = "fnRoundDateTime", 
      Documentation.LongDescription
        = "This rounds a date time to a minute interval that you set (e.g. round to nearest 15 minutes).", 
      Documentation.Examples = {
        [
          Description = "This provides an answer for the following parameters: "
            & "dt = #datetime(2022, 10, 1, 10, 17, 55); "
            & "interval = 15", 
          Code = "fnRoundDateTime(#datetime(2022, 10, 1, 10, 17, 55), 15)", 
          Result = "10/1/2022 10:15:00 AM"
        ]
      }
    ]
in
  Value.ReplaceType(fnRoundDateTime, fnType)