Forum Discussion
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 | |
| 1003 | 03:16:40 | |
| 1004 | 05:23:20 | |
| 1005 | 04:50:00 | |
| 1006 | 03:18:20 | |
| 1007 | 02:27:30 | |
| 1008 | 06:36:40 | |
| 1009 | 04:20:00 | |
| 1010 | 05:43:20 | |
| 1011 | 06:37:30 |
When I try to convert it to Duration, I get the error:
DataFormat.Error: We couldn't convert to Duration.
Details:
00:12:41
I want to convert it so I can calculate the average for each Rep.
Thanks in advance
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
4 Replies
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
Thank you!
It worked. But I wanted to convert to duration in minutes, is it possible?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
NewStep=Table.TransformColumns(PreviousStepName,{"TMA",each Duration.From(Number.From(_))})
