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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ask
Helper III
Helper III

Numbers convert into duration

I have number type column, i want to convert those numbers into duration format : 00:00:00. How to do that ? Thanks

16 REPLIES 16
kcantor
Community Champion
Community Champion

In query editor, select the column and the right click on the column and select change type. then you can select duration from there. It can also be completed on the ribbon. You have to do this in query editor.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@kcantor I did. But it shows like : 

4356.00:00:00

 

I don't think it is right.

What does 4356 represent?  It looks like it is being evaluated as days.  Also, how is it being calculated?

@dkay84_PowerBI

4356 is a number in that duration column. After apply for Duration from Change Data Type, it returns 4356,00:00:00

So is that seconds, minutes, days, etc.?

@dkay84_PowerBI

 

It looks like. But I don't think that is right.

What is the value before you changed the data type to duration?

@dkay84_PowerBI  like 4356, it is a number. I think I might need to do match to get hh:mm:ss

Haha I understand it is a number.  What I'm asking is what is that number?  Days? Seconds? In order to properly calculate duration we can do some simple math to transform it but if you don't know what the granularity is then you have bigger problems than the duration being inaccurate.

I am using Duration = FORMAT(Test[Duration_avg]/60, "hh:MM:SS"), will test if it works well.

MarcelBeug
Community Champion
Community Champion

My prediction is that you will get the time that corresponds with the fraction of your division:

 

Something 4356 - 2.png

 

You reallly need to go back and find out what your value represents, otherwise you are bound to create the most beautiful dashboards with highly unreliable information and you'll be in big trouble sooner or later.

Specializing in Power Query Formula Language (M)

@MarcelBeug

 

The numbers are seconds. How to get the "hh:mm:ss"? 

MarcelBeug
Community Champion
Community Champion

Look at my first post on page 2: #duration(0,0,0,4356)

 

Unlike #date, #datetime and #datetimezone,

#duration allows individual elements (days, hours, minutes, seconds) to exceed the natural limits like <24 and <60.

 

You can even have a mix of positive and negative arguments, e.g. #duration(0,0,2,-90) gives 0.00:00:30 (2 minutes minus 90 seconds).

Specializing in Power Query Formula Language (M)

Thanks all, I have the right numbers by using the DAX in this post

 

https://community.powerbi.com/t5/Developer/formatting-time-value-to-hh-mm-ss/td-p/85249

 

And next  question :

 

How to get the average of duration

 

For example :

 

0.0275
0:42:34
1:05:30
1:10:16

  

I want to get the average duration of above 4 durations in the table.

@MarcelBeug  you are right.  i know couples of way to get "hh:mm:ss".  I need to go back to understand the data better first, and make sure to come up the reliable numbers.

4356 days, 4356 hours, 4356 minutes, 4356 seconds:

 

Something 4356.png

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.