Forum Discussion

luanamoreschi's avatar
luanamoreschi
Frequent Visitor
3 years ago
Solved

Duration Conversion and Calculation

Hello everyone. I have this database where the calls' duration are in the format 00:00:00 (hh:mm:ss). I.E: Contact ID TMA   1000 03:02:30   1001 06:23:20   1002 02:47:30   100...
Need Help
Tips and Tricks
  • Peter_Beck's avatar
    Peter_Beck
    3 years ago

    You might be getting this error becuase you are already automatically converting the data to a time, and then trying to make a duration from that.

     

    If you have an automatic step that is converting the column to a "time", delete that step and replace it with a step that converts it to duration.

     

    This gives an error:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIwtjIwsjI2UIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Contact_ID = _t, TMA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TMA", type time}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"TMA", type duration}})
    in
    #"Changed Type1"

     

    This works ok:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIwtjIwsjI2UIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Contact_ID = _t, TMA = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Source",{{"TMA", type duration}}) // note removed step from previous example
    in
    #"Changed Type1"

     

    Hope this helps,

     

    Peter

     

Peter_Beck's avatar
Peter_Beck
Resolver II
3 years ago

You might be getting this error becuase you are already automatically converting the data to a time, and then trying to make a duration from that.

 

If you have an automatic step that is converting the column to a "time", delete that step and replace it with a step that converts it to duration.

 

This gives an error:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIwtjIwsjI2UIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Contact_ID = _t, TMA = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"TMA", type time}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"TMA", type duration}})
in
#"Changed Type1"

 

This works ok:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIwtjIwsjI2UIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Contact_ID = _t, TMA = _t]),
#"Changed Type1" = Table.TransformColumnTypes(#"Source",{{"TMA", type duration}}) // note removed step from previous example
in
#"Changed Type1"

 

Hope this helps,

 

Peter

 

  • luanamoreschi's avatar
    luanamoreschi
    Frequent Visitor
    3 years ago

    Thank you!

    It worked. But I wanted to convert to duration in minutes, is it possible?

    • Peter_Beck's avatar
      Peter_Beck
      Resolver II
      3 years ago

      Do you mean convert 3 hrs 2 min 30 sec (the example above) to 182.5 minutes?

       

      If so, you can use the functon Duration.TotalMinutes:

       

      https://learn.microsoft.com/en-us/powerquery-m/duration-totalminutes

       

      Alternatively, select the column, and on the Add Column tab, select Duration... and then Total Minutes (it is on the upper right side of the tab - see the screen-shot below). This will create a new column with the total minutes for the column you selected.

       

      Hope this helps,

       

      Peter

      Hope this helps,

       

      Peter