Rounding to nearest 30 min Time. Code errors out if time is after 11:30PM rounding to next day.

[adoster]

Hello. I am using the following code to create a new column to round to the nearest 30 minutes.

= Table.AddColumn(#"Added Conditional Column", "RoundedTime", each Time.From(Number.RoundUp(48*Number.From(Time.From([TABLE_DATE_TIME]))/1)/48))

This errors out if the time is after 11:30PM trying to round to the next day.

Suggestions?

[collinsg]

Here is alternative. It works by

• Calculating how many half hours have elapsed since 00:00:00 on 1/1/1 until the datetime being rounded.
• It rounds up that number of half hours.
• It multiples the rounded up number by a duration of half an hour.
• It adds that to 1/1/1 00:00:00.

= Table.AddColumn(
   #"Name of Previous Step",
   "Rounded Time",
   each
      #datetime(1,1,1,0,0,0) +
      #duration(0,0,30,0) * Number.RoundUp ( 
         ( [Time to Round] - #datetime(1,1,1,0,0,0) ) / #duration(0,0,30,0) 
      ),
   type datetime
)

[artemus]

Note, power query is case sensitive.

Table.AddColumn(#"Added Conditional Column", "RoundedTime", each Time.From(Number.Mod(Number.RoundUp(48*Number.From(Time.From([TABLE_DATE_TIME]))/1)/48), 1))

[adoster]

Error using the above code.

Expression.Error: We cannot convert the value 1 to type Type.
Details:
Value=1
Type=[Type]

[artemus · Accepted solution]

Oh, sorry about that (it is a bit difficult to double check the syntax when I don't have the full query). This should be the correct version:

Table.AddColumn(#"Added Conditional Column", "RoundedTime", each Time.From(Number.Mod(Number.RoundUp(48*Number.From(Time.From([TABLE_DATE_TIME]))/1)/48, 1)), type time)

Note: When I ran the first query I put, I got a different error.

[adoster]

That resolved the issues. Thank you!

[artemus]

Just wrap the number value in a Number.Mod(<num expression>, 1)

[adoster]

I'm still learning. Could you provide an example.

Attempted in error

= Table.AddColumn(#"Added Conditional Column", "RoundedTime", each Time.From(Number.RoundUp(number.mod(48*Number.From(Time.From([DISCHARGE DATE_TIME]))/1)/48)>1))