Convert Text Time to Duration

[mclawler]

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 In	Off Queue	On Queue	Idle
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!

[lbendlin · Accepted solution]

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