Issues with Duration type

[MatH]

Hi,

I have a CSV with columns for thing like talk time, IVR time. Currently Power BI is recognising them as the type of Time, but they should be duration. It's formatted as HH:MM:SS in the file.

When I try to change the type to duration it just gives me an error.

I saw some guidance about splitting the column using the delimiter ':', but this just gave me the hours and then minutes, losing the seconds. I couldn't figure out how to stop this.

How can I change the format so that I can have the talk time as a duration in HH:MM:SS and not as time?

Thanks for your help.

[Ahmedx]

Is this what you are looking for?

[MatH]

Not really, I'm happy for them to stay in HH:MM:SS formatting similar to a time on the clock. But I want to be able to report on these durations so we can know the average call length is X, the wait time is Y for example.

At the moment with them being times it's just giving me options like Earliest, or Latest on the card. I managed to get it to convert to duration by moving the type change as the file was loaded, this gave me what I wanted: 

But when I close and apply the query it then converts them to a decimal number and breaks that duration. I can't understand why?

[v-jingzhan-msft · Accepted solution]

Hi @MatH 

This is because the Duration data type is not supported in Report view. It only exists in Power Query Editor currently.

The Data Type dropdown selection in Power Query Editor has two data types not present in Data View or Report View: Date/Time/Timezone and Duration. When you load a column with these data types into the Power BI model, a Date/Time/Timezone column converts into a Date/time data type, and a Duration column converts into a Decimal number data type. The decimal number represent a duration in Day unit. 

Based on above, a common practice at present is to use DAX to convert the decimal number into hours or minutes or seconds for calculating the average/max/min/.... Then format the result into Text type to make them display similar to a time. But this practice has a problem if you want to display them in visuals which expects numeric values. 

Or you can try another workaround from Chelsie Eiden's Duration - Microsoft Fabric Community. It resolved the above concern. 

Hope this will be helpful. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Ahmedx]

Can you please share your demo input and expected output!

[MatH]

Thanks for your reply, I have the two columns below where the data is HH:MM:SS as exported from the database.

It's formated at time currently, but it should be duration as it's call length data so 02:30:00 should be 2 hours 30 minutes, and not 02:30AM for example.

I just want the duration so I can do things like look at the average/spread etc.

I tried changing the data type but that just gave me an error, I also tried following a guide to split the column with the hope of then rejoining them as duration but it would split giving me the HH and the MM but totally ignoring the SS.