locked
Round time to nearest 30 minutes RRS feed

  • Question

  • My data has a DateTime field with values such as:

    10/12/2011 2:56:07 PM
    10/12/2011 2:58:58 PM

    I need to group my data by 30-minute intervals in Power Query. I'm not sure how to do this. I can use:

    Time.Minute( [StartDate] ) 

    This returns the minutes. But what I need is to say something like "If minutes > 29 then 30 else 00", and combine it with the date and hour value so I get a datetime back that tells me what 30-minute period the time value fell into (both of the above would fall in the 2:30 block).

    I do this a lot in Excel with formulas, but can't figure out how to do it in Power Query.


    Shawn Keene

    Thursday, August 7, 2014 6:33 PM

Answers

  • [StartDate]+#duration(0,0,if Time.Minute([StartDate])>29 then 30-Time.Minute([StartDate]) else 0-Time.Minute([StartDate]),0)
    Thursday, August 7, 2014 9:19 PM

All replies

  • This seems to get me a time value.

    Time.FromText ( Text.Combine({Text.From(Time.Hour([StartDate])),if Time.Minute( [StartDate] ) > 29 then "30" else "00"}, ":") 

    Now, how to add the original date to it?  I tried this, but it didn't work

    DateTime.Date([StartDate]) + Time.FromText ( Text.Combine({Text.From(Time.Hour([StartDate])),if Time.Minute( [StartDate] ) > 29 then "30" else "00"}, ":") )


    Shawn Keene

    Thursday, August 7, 2014 6:54 PM
  • [StartDate]+#duration(0,0,if Time.Minute([StartDate])>29 then 30-Time.Minute([StartDate]) else 0-Time.Minute([StartDate]),0)
    Thursday, August 7, 2014 9:19 PM