Duration Conversion and Calculation

[luanamoreschi]

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

[Peter_Beck · Accepted solution]

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]

Thank you!

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

[Peter_Beck]

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

[wdx223_Daniel]

NewStep=Table.TransformColumns(PreviousStepName,{"TMA",each Duration.From(Number.From(_))})