Convert Text Time Duration

[mclawler]

I reviewed various previous threads on this matter, but they all seemed to confuse me even further.  

When I import my Excel report it stores the data below as text, no matter what I format them to within Excel itself.  These show H:M:S. - as an example for Logged In it's showing 8 hours:55 minutes:48.197 seconds

Logged In	Off Queue	On Queue	Idle
08:55:48.197	01:51:28.360	07:04:19.837	02:36:25.481

I am trying to visualize cards that will show Total Logged In time, Average Logged In time, etc. sliced by a date slicer.  But when I use the fields above within PowerBI, there's no SUM or AVG options just First/Last.  

Please advise how to convert these text fields, I assume a calculated column is in order.  Whenever I try to convert data type from text to decimal number it tells me it can't automatically convert.  I have lots of columns within this report that need this conversion generated.  

Thank you for your time and support!

[CoreyP]

Have you tried using the Duration functions in Power Query? 

[mclawler]

I think I found it

Even if I choose minutes though, and then it generates a decimal number.  Is there an easy way to convert that decimal number to display in the visual card something like 8:35 for 8 hours and 35 minutes?  It seems like this decimal is always going to give me a decimal type visual.  Which would work, but cosmetically the 8:35 would look better than 8.5x hours if that makes sense... 

[CoreyP]

Yeah, so you will need the data type to be in a number format so you can create your aggregation measures. But then you can do some calculations and dynamic format string to put it back into the HH:MM:SS text format you're looking for. 

[mclawler]

I'm a rookie so bare with me 🙂  But I found the Duration Function within Power Query and it changed the data to a decimal number.  You can see the comparison here between a converted column and a non-converted column.  But what is this decimal telling me? A fraction/decimal of how many days/hours?  If I know the basis of the decimal I think I can then convert to hours/minutes/seconds?  

Thank you so much already!! 

[CoreyP · Accepted solution]

Try reading through this chapter on all the duration functions: https://learn.microsoft.com/en-us/powerquery-m/duration-functions.

I think you'll want to convert to duration from text first. Then parse out your hours, minutes, seconds and either aggregate them separately or convert them all into the same unit and then aggregate. 

[mclawler]

That's perfect!!  Thank you, I think you've got me on the right track now.  Much appreciated!