Forum Discussion
Helper ITime format m:ss to hh:mm:ss
Hello,
I have start-time data that looks like the below "Current" column and I need to transform it into the "Desired Result" column:
Current Desired Result
124 12:01:24 AM
3440 12:34:40 AM
33218 03:32:18 AM
232427 11:24:27 PM
Additionally, using the "Units Loaded" column (not shown above) I need to show units loaded per hour/day/month, etc. Each row has a "Units Loaded" value along with the time data from above.
End Result Visual:
How can I achieve this?
Sorry! I actually had to go to my desk to get it right. Code below:
let
Source = {124, 3440, 33218, 232427},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Current"}}),
#"Changed Type" = Table.TransformColumns(#"Renamed Columns",{{"Current", Text.From}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Current", Splitter.SplitTextByPositions({0, 2}, true), {"Current.1", "Seconds"}),
#"Split Column by Position1" = Table.SplitColumn(#"Split Column by Position", "Current.1", Splitter.SplitTextByPositions({0, 2}, true), {"Hours", "Minutes"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Position1","","0",Replacer.ReplaceValue,{"Hours", "Minutes", "Seconds"}),
#"Changed Type1" = Table.TransformColumns(#"Replaced Value",{{"Hours", Number.From}, {"Minutes", Number.From}, {"Seconds", Number.From}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Time", each #time([Hours], [Minutes], [Seconds]), type time)
in
#"Added Custom"--Nate
6 Replies
Sorry! I actually had to go to my desk to get it right. Code below:
let
Source = {124, 3440, 33218, 232427},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Current"}}),
#"Changed Type" = Table.TransformColumns(#"Renamed Columns",{{"Current", Text.From}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Current", Splitter.SplitTextByPositions({0, 2}, true), {"Current.1", "Seconds"}),
#"Split Column by Position1" = Table.SplitColumn(#"Split Column by Position", "Current.1", Splitter.SplitTextByPositions({0, 2}, true), {"Hours", "Minutes"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Position1","","0",Replacer.ReplaceValue,{"Hours", "Minutes", "Seconds"}),
#"Changed Type1" = Table.TransformColumns(#"Replaced Value",{{"Hours", Number.From}, {"Minutes", Number.From}, {"Seconds", Number.From}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Time", each #time([Hours], [Minutes], [Seconds]), type time)
in
#"Added Custom"--Nate
First, change the number column to text, then split the column like this.:
=Table.SplitColumn(TableName, "Current",
Splitter.SplitTextByRepeatedLengths(2, true), {"Hours", "Minutes", "Seconds"}))
-Note, I'm not sure what order they will come out as, cause I'm outside, but you can rename as needed.
Make sure that you change the new columns back to numbers, then it's just:
Table.AddColumn(PriorStepName, "Time", each #time([Hour], [Minutes], [Seconds]))
--Nate
Blues88I tried to create a custom column and all the data came out as an error. See below.
- mahoneypat
Microsoft Employee
You could also use a DAX column expression like this to get the column to be used on your X axis. Replace Times[Current] with your actual Table[Column].
NewColumn = var thisnumber = Times[Current]var rounded = MROUND(thisnumber, 10000)return FORMAT(rounded, "#0:00:00")Pat - Blues88
I wasn't able to get either of these solutions to work. Could be an error on my end.
However, I solved by adding a custom column in M that added leading zeroes to all numbers to make it a string of 6 and then used this formula to add a new column in DAX:
= (LEFT(TIMELOG[Add Zeroes],2)+(MID(TIMELOG[Add Zeroes],3,2)/60)+(RIGHT(TIMELOG[Add Zeroes],2)/3600))/24It worked.
I appreciate your replies. Have a great day!
