Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
3 years ago
Solved

Change HHMMSS to Time

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?

Need Help
  • JEC's avatar
    JEC
    3 years ago

    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

     

     

1 Reply

  • JEC's avatar
    JEC
    Regular Visitor
    3 years ago

    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