Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
manoji
Regular Visitor

Convert a number to 24 Hour time

manoji_0-1625148668131.png

Hi Team,

 

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

Thanks Guys. 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

See if this works @manoji. It returns this:

 

edhans_0-1625151149550.png

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.

edhans_1-1625151343261.png

 

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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
manoji
Regular Visitor

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

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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. 

@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?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

See if this works @manoji. It returns this:

 

edhans_0-1625151149550.png

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.

edhans_1-1625151343261.png

 

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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors