Duration more than 24 hours: transform the duration to HH:MM:SS

[LPad]

I have a CSV file with column in HH:MM:SS format that has time greater than 24 hours; the time is coming into the model as HH:MM:SS Am/PM. When I try to change the column type to "duration", I am getting an error because of values that are larger than 24 hours. Need help converting this duration time. 

[Anonymous]

let
    dft = (text)=>
    let 
        H=List.Transform(Text.Split(text,":"),Number.FromText),
        g=Number.IntegerDivide(H{0},24),
        h= H{0}-g*24,
        m=H{1},
        s=H{2}
    in
        #duration(g,h,m,s)
in 
    dft

let
    dft = (text)=>
    let 
        H=List.Transform(Text.Split(text,":"),Number.FromText),
        g=Number.IntegerDivide(H{0},24),
        h= Number.Mod(H{0},24),
        m=H{1},
        s=H{2}
    in
        #duration(g,h,m,s)
in 
    dft

[AlexisOlson]

FYI, you can do this without the mod and integer divide too.

Add a new custom column like this:

Table.AddColumn(
    Source,
    "Custom", each
        let
            H = List.Transform(Text.Split([Column1], ":"), Number.FromText)
        in
            #duration(0, H{0}, H{1}, H{2}),
    type duration
)

[LPad]

Thank you for your help! when applying this solution, changed the data to Date + Time and errors when trying to convert it to duration. Any further suggestions.

Total Handle Column

[AlexisOlson · Accepted solution]

Don't change it to Date+Time convert to duration direction from text.

Here's a full sample query you can paste into the Advanced Editor of a new blank query:

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

[PaulMac]

@AlexisOlson 

This is a good start but failed to mentioned how you then display figures as Days, Hours, Minutes, Seconds as a visiual in the report side of power BI. This is the real answer that is needed.

Thanks
Paul

[alex]

You @-ed the wrong alex :).  You want @AlexisOlson  😀

[PaulMac]

Thanks for the correction @alex