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 to within Excel itself.  These show H:M:S. - as an example for Logged In it's showing 8 hours:55 minutes:48.197 seconds

 

Logged InOff QueueOn QueueIdle
    
    
 08:55:48.197 01:51:28.360 07:04:19.837 02:36:25.481

 

I am trying to visualize cards that will show Total Logged In time, Average Logged In time, etc. sliced by a date slicer.  But when I use the fields above within PowerBI, there's no SUM or AVG options just First/Last.  

 

Please advise how to convert these text fields, I assume a calculated column is in order.  Whenever I try to convert data type from text to decimal number it tells me it can't automatically convert.  I have lots of columns within this report that need this conversion generated.  

 

Thank you for your time and support!

@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".

1 Reply

  • 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".