Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LPad
Regular Visitor

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

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. 

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

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

 

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

 

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
)

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

LPad_0-1650565252547.png

 

 

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"

@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

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

Thanks for the correction @alex 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors