Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
adoster
Resolver I
Resolver I

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?

1 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.

View solution in original post

7 REPLIES 7
collinsg
Super User
Super User

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
)

 

collinsg_0-1684529447591.png

 

artemus
Employee
Employee

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

 

Error using the above code.

 

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

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.

That resolved the issues. Thank you!

artemus
Employee
Employee

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors