Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!