Power Query - Error while rounding off time

[galbatrox9]

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)

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

[Tad17]

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.

[galbatrox9]

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.

[Tad17]

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

[galbatrox9]

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

[Tad17]

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

[galbatrox9]

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 

[Tad17 · 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.

[Tad17]

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.