Convert a number to 24 Hour time

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.