Forum Discussion

misunika95's avatar
misunika95
Frequent Visitor
6 years ago
Solved

Duration conversion issues

Hi Everyone!   I have some data that reflects how long a person took to resolve a dispute. The format is hh:mm (for example 1328:45) and when I try to change the data type to duration the following...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • artemus's avatar
    artemus
    6 years ago

    You can use the following custom step:

    = Table.TransformColumns(PreviousStep,{{"ColumnName", each let parsed = Text.Split(_, ":") in #duration(0, 0, Int64.From(parsed{0}), Int8.From(parsed{1})), type duration}})

    Just change PreviousStep and "ColumnName" to appropiate values.

artemus's avatar
artemus
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

You can use the following custom step:

= Table.TransformColumns(PreviousStep,{{"ColumnName", each let parsed = Text.Split(_, ":") in #duration(0, 0, Int64.From(parsed{0}), Int8.From(parsed{1})), type duration}})

Just change PreviousStep and "ColumnName" to appropiate values.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi artemus ,

 

Thank you for the solution, it helped fix a similar issue i had with my numbers. I do have a quick question about the logic.

 

So, using your formula, i see a cell which was previously text(hh:mm:ss) 1594:44:28 changes to a duration of 1.02:34:44 (ddd.hh:mm:ss) post applying this logic. I am a bit confused, i was expecting to see around 66 days instead of the 1.02 like above.

 

It'd greatly help if you can explain the logic of the custom step. 

 

Thanks for your time.

 

-K

  • artemus's avatar
    artemus
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    The formula I gave only works for mm:ss, not hh:mm:ss. It treated your 1594:44:28 as 1594:44

     

    If you had that format you could use:

    #duration(0, Int64.From(parsed{0}), Int8.From(parsed{1}, Int8.From(parsed{2})

     

    This would only work if you always had the hour component (event if it was 0). If you wanted to support both formats would you need to:

     

    if parsed{2}? = null then #duration(0, 0, Int64.From(parsed{0}), Int8.From(parsed{2})) else #duration(0, Int64.From(parsed{0}), Int8.From(parsed{1}), Int8.From(parsed{2}))

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for taking the time to reply. I am running into this error at the else part of your code. 

       

      I am pretty new to M code, can you please help correct any syntax issues i might have overlooked?

       

      = Table.TransformColumns(#"Reordered Columns",{{"Time to Accept", each let parsed = Text.Split(_, ":") in if parsed{2}? = null then #duration(0, 0, Int64.From(parsed{0}), Int8.From(parsed{2}) else #duration(0, Int64.From(parsed{0}), Int8.From(parsed{1}, Int8.From(parsed{2}), type duration}})

       

      Error -

       

      Thaks again for your time.

       
       

      • Steven_Jia's avatar
        Steven_Jia
        New Member
        3 years ago

        I have the same SyntaxError. Did you have the solution?