Forum Discussion
how to parse duration
hello all! i have the data in the column in the following format - DD:HH:MM:SS (days, hour, minutes, seconds). How do I parse it to duration in PowerBI and/or to seconds (because later I'd like to summarize that)? Thank you.
Anonymous , please check if these can help
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
Hi Anonymous,
In my opinion, I'd like to suggest you split duration type value into multiple fields to store different units or transform them into one level (e.g. total seconds), it should help for the Dax calculations. You can simply create a text field with DAX formulas to show the raw duration values. (notice: raw duration type not supported in power bi data mode tables)
Regards,
Xiaoxin Sheng
6 Replies
Hi Anonymous ,
The dd.hh:mm:ss format is duration. There is a specific Duration data type in Power Query that you can convert this to.
If you mean you want to convert to decimal time, you can change the data type in Power Query to decimal. This will convert a duration of 2.15:25:21, for example, to 2.6426... days. Once you have the duration in decimal days, then it's just a case of multiplying by 24 for hours, 1,440 for minutes etc.
Pete
Anonymous , please check if these can help
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
Using the Chelsie Eiden's Duration in this post I was able to arrive at the sum of the hours in Power BI (step 1). The issue is that I don't have as instead of the sum being able to use the average time.
Do you know if there's a way to do it?
Hello you found the solution also I want to average it
Hi Anonymous,
In my opinion, I'd like to suggest you split duration type value into multiple fields to store different units or transform them into one level (e.g. total seconds), it should help for the Dax calculations. You can simply create a text field with DAX formulas to show the raw duration values. (notice: raw duration type not supported in power bi data mode tables)
Regards,
Xiaoxin Sheng
Hello! I have a similar problem.. maybe someone can help me with this: I have a table extracted from Access that summarizes the time it took to perform a series of "x" jobs. What I want is to dump it into Power BI so I can use that data.
I use Power Query and assign it a Durration format ("dd. hh.mm.ss") but when I take it to Power BI (for a visulaization) it limits the number of possible summaries to Count and Distinct Count.
I need the average because what I want to see is just this data.
Anyone know how I can do it?
