Forum Discussion

cheid_4838's avatar
cheid_4838
Icon for Helper IV rankHelper IV
2 years ago
Solved

Text to Duration Convertion

I have a report that comes from a 3rd party system that measures engine performance.  In this report there are a few columns that represent time durations.  When I convert the columns to duration I get a lot of errors (see screenshot) for some of the values in the columns.  I have converted many values to duration in the past, but never encountered this issue.  Is there a trick to getting these to convert to duration? Thanks.

 

 

 

 

 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    2 years ago

    cheid_4838 

     

    Impossible to tell you the cause without seeing your data, or guessing.  The errors coudl be because the data you are tyrying to convert are text, or dates or ......

     

    If you click on an Error, it will tell you what the error is.  You can then fix the cause.

     

    Please supply the data you are trying to convert.

     

    Phil

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    2 years ago

    cheid_4838 

     

    Impossible to tell you the cause without seeing your data, or guessing.  The errors coudl be because the data you are tyrying to convert are text, or dates or ......

     

    If you click on an Error, it will tell you what the error is.  You can then fix the cause.

     

    Please supply the data you are trying to convert.

     

    Phil

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

    I agree with PhilipTreacy , it's always better to see the error. However, it looks like the failing values are bursting the limits of the duration.  For example, the 3 rows above 2:38:11 have 102, 77 and 65 for the hours and Power Query needs them to be between 0 and 23.

    It's a bit of extra effort to parse these hour values into days and hours.

    --

    I have a feeling that it won't solve the challenges because powerbi doesn't really like durations in the front end.

    • cheid_4838's avatar
      cheid_4838
      Icon for Helper IV rankHelper IV
      2 years ago

      That solution did not work.  It doesn't like the high numbers above 24.  I am surprised these can't be converted to at least decimals then back into the time that way.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, cheid_4838 

    First, thanks for HotChilli and PhilipTreacy  help. This is indeed caused by the fact that Power Query's Time type doesn't support data longer than 24 hours, you can use the following method.


    1. Change type

    2. Change 0:1:0 to 00:01:00

    3. Result

    = Table.TransformColumnTypes(Source,{{"Duration", type text}})

= Table.TransformColumns(#"Changed Type", {"Duration", each 
    let 
        parts = Text.Split(_, ":"),
        hh = Text.PadStart(parts{0}, 2, "0"),
        mm = Text.PadStart(parts{1}, 2, "0"),
        ss = Text.PadStart(parts{2}, 2, "0")
    in
        hh & ":" & mm & ":" & ss
})

= Table.TransformColumns(#"Custom1",{"Duration", each 
    let 
        txt = _,
        h = Number.From(Text.Start(txt,2)),
        mm = Number.From(Text.Middle(txt,3,2)),
        ss = Number.From(Text.End(txt,2)),
        dd = Number.IntegerDivide(h,24),
        hh = Number.Mod(h,24)
    in
        #duration(dd,hh,mm,ss), type duration
})

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum