Forum Discussion

mclawler's avatar
mclawler
Helper III
2 years ago
Solved

Convert Text Time to Duration

I reviewed various previous threads on this matter, but they all seemed to confuse me even further.     When I import my Excel report it stores the data below as text, no matter what I format them ...
@Daxhelp
duration
  • lbendlin's avatar
    lbendlin
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcnJCcAwDATAVoLeQei019uKcf9tJCTPYfaWy8BuFtTXlPu1s50BzWGfJ63oS5H/B3MwWgsu5zw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Logged In" = _t, #"Off Queue" = _t, #"On Queue" = _t, Idle = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", type duration}})
in
    #"Changed Type"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

lbendlin's avatar
lbendlin
Super User
2 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcnJCcAwDATAVoLeQei019uKcf9tJCTPYfaWy8BuFtTXlPu1s50BzWGfJ63oS5H/B3MwWgsu5zw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Logged In" = _t, #"Off Queue" = _t, #"On Queue" = _t, Idle = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", type duration}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".