cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Power Query - Error while rounding off time

Hi Team,

 

I wanted to round off my time column into the nearest hour in Power Query and it worked for most of the data, but about 20% of the data has errors now and I am not able to understand why. 

Here's a snapshot and this is the code I used : Time.From(Number.Round(Number.From([IST Time])*24)/24)

Capture.JPG

 

Can anyone help me understand how to fix this? Or suggest a better code to use.

1 ACCEPTED SOLUTION

Well you can use the DAX to create a caluclated column on your original table or recreate the table as a visual and add a column for the measure. Should make it fairly easy to determine if there were any errors.

View solution in original post

8 REPLIES 8
Solution Sage
Solution Sage

Hey @galbatrox9 

 

You don't have to use DAX for this. I would just duplicate the column, retitle it, and then in query editor change the format to HH under the Transform tab. See this article for more information: https://docs.microsoft.com/en-us/dax/custom-date-and-time-formats-for-the-format-function

 

If this helps please kudo.

If this answers your question please accept it as a solution.

Hi @Tad17 ,

 

Thats neat, but what if i wanted to round to the nearest hour? In your way, 10:45 PM would be converted to 10 PM, but i want it to come to 11 PM.

My bad. I forgot formatting doesn't round. In that case you were on the right track. The answer you're looking for is in this post: https://community.powerbi.com/t5/Desktop/How-to-round-minutes-in-time-to-the-nearest-hour/td-p/47643...

Haha the code I used was exactly from that link! But its giving me errors on some lines.

Haha I see that now. Have you tried the DAX solution instead of the M option?

 

If it has to be M try this thread: https://community.powerbi.com/t5/Desktop/Round-Down-to-Nearest-30-Minute-Interval-Query-Editor/td-p/...

I just tried the Dax solution. Do you know a way to check if there are errors in that? From the filter dropdown, i cant see any errors. @Tad17 

Well you can use the DAX to create a caluclated column on your original table or recreate the table as a visual and add a column for the measure. Should make it fairly easy to determine if there were any errors.

View solution in original post

Only concern with this is I believe it makes it based on a 24 hour clock and I'm not sure how to convert it to 12 hour if that is what you need.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors