Forum Discussion

adoster's avatar
adoster
Resolver I
3 years ago
Solved

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

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?

Need Help
  • artemus's avatar
    artemus
    3 years ago

    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.

7 Replies

  • artemus's avatar
    artemus
    Microsoft Employee
    3 years ago

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

    • adoster's avatar
      adoster
      Resolver I
      3 years ago

      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))

  • artemus's avatar
    artemus
    Microsoft Employee
    3 years ago

    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's avatar
      adoster
      Resolver I
      3 years ago

      Error using the above code.

       

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

      • artemus's avatar
        artemus
        Microsoft Employee
        3 years ago

        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.

  • collinsg's avatar
    collinsg
    Solution Sage
    3 years ago

    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
    )