Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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.

2 ACCEPTED SOLUTIONS
v-shex-msft
Community Support
Community Support

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)

Aggregating Duration Time  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
Syndicate_Admin
Administrator
Administrator

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?

v-shex-msft
Community Support
Community Support

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)

Aggregating Duration Time  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.