Time format m:ss to hh:mm:ss

[Blues88]

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?

[watkinnc · Accepted solution]

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

[watkinnc]

Are you first changing these to text values before splitting?

[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))/24

It worked.

I appreciate your replies. Have a great day!

[mahoneypat]

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

[watkinnc]

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

[Blues88]

I tried to create a custom column and all the data came out as an error. See below.