24 HOUR Data and Closet to the Hour

[bdehning]

I had a Time Column which I used to convert a specific military time to the nearest hour using a Custom Column.    I used the following

=Time.From( Number.Round( Number.From( [Time of Incident] ) * 24 ) / 24 )

I got one error when one time was 11:45 PM but everytning else worked.   

How do I get POWER BI to use show 24:00 and not show and error?

[ryan_mayu]

@bdehning 

I don't think we have 24:00, does that showed 00:00, 0-23 are 24  numbers and represent 24 hours.

[bdehning]

I get rows from 00:00 all the way to 23:00.  There must be way to represent 24:00 when time falls between 11:30 pm and 12 AM?

[ryan_mayu]

@bdehning 

you can try this, but the output is not time type

try Time.From( Number.Round( Number.From( [Time of Incident] ) * 24 ) / 24 ) 
otherwise "24:00:00"

[bdehning]

I can get the otherwise "24:00:00" to work at the end of my fomula.  It keeps saying Token Eof expected.   

[ryan_mayu]

@bdehning 

could you pls provide the error screenshot?

[bdehning]

[ryan_mayu]

you missed try.

try Time.From( Number.Round( Number.From( [Time of Incident] ) * 24 ) / 24 ) 
otherwise "24:00:00"

[bdehning]

It ran but after going back to Power and checking clustered bar chat it reverted times back to AM and PM and I lost rows like 00:00 because it showed 12:00 am.

Do I need a new custom column once I get the 24:00 to show up to get the 00:00, 01:00, 02:00 I am looking for??

[ryan_mayu]

@bdehning 

it should not change because the output is text not time type.

[bdehning]

I am still playing with this.  This is an issue with times that fall on  :30:00.  Most as expected round up to the next higher hour but not all.  8:30:00 PM went to 8:00:00 and not 9:00:00 as expected.  4:30:00 AM when to 4:00:00 AM and not 5:00:00 AM.  What do I need to do to get all time with :30:00 to go up to the next hour?

[ryan_mayu]

@bdehning 

have you tried to change round to rounddown?

Number.RoundDown - PowerQuery M | Microsoft Docs

[bdehning]

Same thing   what am I missing?

[ryan_mayu]

pls add the letter "try" in the formula

= try Time.From( Number.Round( Number.From( [Time of Incident] ) * 24 ) / 24 )
otherwise "24:00:00"