cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
luanamoreschi
Frequent Visitor

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 IDTMA 
100003:02:30 
100106:23:20 
100202:47:30 
100303:16:40 
100405:23:20 
100504:50:00 
100603:18:20 
100702:27:30 
100806:36:40 
100904:20:00 
101005:43:20 
101106: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

 

 

1 ACCEPTED SOLUTION
Peter_Beck
Resolver II
Resolver II

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

 

View solution in original post

4 REPLIES 4
Peter_Beck
Resolver II
Resolver II

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.

 

ScreenHunter_117 Mar. 14 20.58.jpg

Hope this helps,

 

Peter

Hope this helps,

 

Peter

wdx223_Daniel
Super User
Super User

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

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!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors