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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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