Formatting durations that come in as durations.

[telusive]

Hello! Currently I am connecting to SolarWinds, which is some sort of IT ticketing software. They have a to_resolve_business_time that is exactly that, the duration from the creation of the ticket to the resolution. This can be several hours. More than 24. Which is where I am encountering an issue. What do I do with this to format it as a duration so that I can use it for averaging etc? Anything over 24 hours is considered an Error. 

What can I provide to make this easier? 
It imports like this:

If it's text, I can't do any math as a duration, as I'd like to do average/median/etc. 

Thanks!

[DataInsights · Accepted solution]

@telusive,

Try this in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjC0MjC1MjJVitUBcgysDEysjIzAHCMzKxDfQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [to_resolve_business_time = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"to_resolve_business_time", type text}}),
    Duration = each let d = List.Transform(Text.Split(_, ":"), Number.FromText) in #duration(0, d{0}, d{1}, d{2}),
    TransformColumn = Table.TransformColumns(ChangeType, {"to_resolve_business_time", Duration, type duration})
in
    TransformColumn

Raw data (text):

Result (duration):