Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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(_))})
Check out the July 2025 Power BI update to learn about new features.