Change HHMMSS to Time

[joshua1990]

I have a column that contains the time as HHMMSS like 120530 for 12:05:30 or 30503 for 03:05:03.

How is it possible to change this to a data type of time?

[JEC · Accepted solution]

Input

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cType = Table.TransformColumnTypes(Source,{{"time", Int64.Type}}),
    convert = Table.TransformColumns(cType,{{"time", (x)=> let t = Text.From(x) in List.Accumulate({4,2}, if Text.Length(t) = 5 then "0" & t else t , (s,c)=>  Text.Insert(s,c,":"))}}),
    out = Table.TransformColumnTypes(convert,{{"time", type time}})
in
    out

 Output