Convert a number to 24 Hour time

[manoji]

Hi Team,

I need support here. How can I convert these numbers in 'Time' to 24H time in a separate column? 

Thanks Guys. 

[manoji]

you guys are legends mate. all the best. wishing you guys healthy long life. 

[watkinnc]

You could just do this:

= Table.AddColumn(PriorStepName, "24HR Time", each if [Time] = 24 then #time(0,0,0) else Time.From([Time]/24]), type time)

--Nate

[manoji]

I had some issues with this code mate, but I solved my problem with another solution here. Thank you for the support. If possible, Can you look into this code again? I would love to try this too. 

[edhans]

@manoji - I'm not clear what you mean that you had problems with this code. The core solution was just the #time([Time], 0, 0) function.

What problems did you have?

[edhans · Accepted solution]

See if this works @manoji. It returns this:

The code is this:

let
    Source = {1..24},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Time"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Time", Int64.Type}}),
    #"Added Time Stamp" = Table.AddColumn(#"Changed Type", "Time Stamp", each #time([Time], 0, 0), type time),
    #"Added Time Text" = Table.AddColumn(#"Added Time Stamp", "Time Text", each Time.ToText([Time Stamp], "H:MM:ss"), type text)
in
    #"Added Time Text"

The #time() function converts it to a time using the integer as the hour. It will convert 24 to midnight as long as there are no minutes and seconds. So #time(24,0,0) works, #time(24,1,0) will not.

So essentially the below in a new custom column. I've just included a full query above for you to look at.

= #time([Time], 0, 0)

The display of am/pm is based on your local machine settings. But you can convert the visible value to 24hr format using the Time.ToText function in the next column. Once you load into Power BI, you can also change the format of the Time Stamp column using the h:nn format.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.