Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
Hi Team,
I need support here. How can I convert these numbers in 'Time' to 24H time in a separate column?
Thanks Guys.
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingyou guys are legends mate. all the best. wishing you guys healthy long life.
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 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?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSee 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingStarting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
42 | |
25 | |
14 | |
14 | |
12 |