Converting elapsed time stored as text to duration

[Anonymous]

I need to be able to work with the elapsed time but it is stored as text in the format day : hour : minute. I've tried to change the type but it ends on an error. I need to display it as it is, day : hour : minute, on Power Bi, but in a way that allows me to work with it as if it were stored as duration. In other words, I need to know how to change the type but I don't know how.

[v-jingzhang]

Hi @Anonymous 

The Duration data type is only available in Power Query Editor. It is not supported in Data or Report view. When a query is loaded into the model and viewed in Data or Report view, a column with a Duration data type is converted into a Decimal Number. This is by design currently. You can refer to Data types in Power BI Desktop for details. As a Decimal Number type it can be added or subtracted from a Date/Time field with correct results. As a Decimal Number type, you can easily use it in visualizations that show magnitude.

If you want to display it in the format "day:hour:minute" in the report, you need to keep it as the Text format. If you want to make calculation on the elapsed time at the same time, you can add a new column by converting the text column into a decimal number column. You could have two columns in the table, the text column is for display and the other decimal number column is for calculation. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Hello  v-jingzhang

Thanks for your explanation and your time. This is the table I'm working with. I have start time 1 and 3, end time 1 and 3, and the elapsed time 1 and 3 (this is the elapsed time I mentioned before).

Elapsed time is calculated from the subtraction between end times and start times. As I wasnt able to work with the elapsed time columns, this is what I did. I copied the columns and extracted the time only, then I did the subtraction, and as there were null elements I replaced them with 0.00:00:00 to be able to do add up both spent times. But I found out an issue, theres negative times, this is because an activity starts certain day and ends the next day. If we ignored those negatives times, what would happen is what you explained, on Power Bi the spent times are shown as decimal numbers because they are formated as duration on Power query. 

Spent time 1.1 and 3.3 are supposed to add up because they represent the total time a person took to end several activities, but because of the negative time is not possible. Is there a way to correct this issue? and is there a way to convert the decimal number to "hours:minutes" and keep been able to do operations? This is beacause I need to show through graphs the time a person took to complet all of the assined activities and a decimal would mean nothing and it would be hard to ty to understand it.

Lets say there are no negative times, and this what I figured out what I could do. I created two copies from the total time, which is the sum of elapsed time 1.1 and 3.3, and then extracted the hours and minutes separately. Is there a way to merge those columns on Power Bi through some code to be shown as "hours:minutes"? if so, could you show me the way or refer to a page where I could learn how to do it? I could keep them as whole number and do a conversion when added minutes exceed 60 minutes.

 

[v-jingzhang · Accepted solution]

Hi @Anonymous 

The limitation is that it is not possible to convert the durations to the format "hours:minutes" while keep them as decimal number type for calculation. To display as "hours:minutes", they will be converted into Text data type. Text durations can display well in Table, Matrix and Card visuals. However, if you want to display it in visuals like Line Chart, Column Chart... which require numeric fields, you need to use the decimal number column. Text type fields are not able to be used as Values in those visuals. You can only add the text column to the tooltip. 

I attached a sample pbix at bottom, which has some calculation in it. Hope it would be helpful. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Thanks this is was really helpfull

[edhans]

You've provided no example of what you are working with @Anonymous 

And Power BI cannot work with duration in the data model. That is a Power Query only thing, and must be converted to a valid data type (number, date, etc) in Power Query. Durations mean nothing to DAX.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.