Converting whole number to HH:MM:SS

[Randall]

Hi! New user to Power BI here and trying to learn how to convert a whole number to a HH:MM:SS format. I've done quite a bit of research but can't seem to find an exact solution online for what I need. I'm currently screening transaction ledgers and the date/time stamp is significantly important for analyzation. However I am able to get this to work, it will eventually apply to about 75 million rows of data.

The first number I have in the [Time] column is 90403 after drawing out the data from our system.

I have attempted to create a custom column and do = #duration(0,0,0[Time]) and then try to manipulate it from there to no avail.

Any help would be greatly appreciated and if I need to provide more data, please let me know! Thank you!

 

[Randall]

So through extensive trial and error... I've discovered that 90403 is in fact the actual time stamp. It is 9:04:03. Is there a way to convert this text into the Time format required? I've tried converting to text then Time. I've attempted Time.FromText with no luck as well.

 

[JGordon11]

Here's an example of a couple of ways of converting the format:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQwMTBWitWJVjI0MDQ1tYQwjQ2MDAzBTCNjU0uQaCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
    
    //add a column of data of type time (fraction of a day) formatted as H:MM:SS AM/PM
    tbl = Table.AddColumn(Source,"HMMSS AM/PM", each 
        let 
            n = Text.PadStart(Text.From([Time]),6,"0"),
            h = Number.From(Text.Start(n, 2)),
            m = Number.From(Text.Middle(n, 2,2)),
            s = Number.From(Text.End(n,2))
        in 
            #time(h,m,s)),
    
    //add a column of type text formatted as HH:MM:SS (24 hr clock)
    Result = Table.AddColumn(tbl,"HMMSS (military)", each 
        let 
            n = Text.PadStart(Text.From([Time]),6,"0"),
            h = Text.Start(n, 2),
            m = Text.Middle(n, 2,2),
            s = Text.End(n,2)
        in 
            h & ":" & m & ":" & s)
in
    Result