Duration on Query Editor vs Time on Visual

[Anonymous]

Hi Folks!

Recently a friend asked me about durations with more than 24 hours. The point is there is an Excel workbook with a worksheet containing a column that represents a duration, but some values are over 24 hours.

When I imported this worksheet to the Power BI and tried to change the data type to "Duration" on Query Editor, I received an error on these values.

Then, I tried to calculate the duration using the Query Editor and then I successfully changed the data type to duration.

The format presented is something like that:

7.10:28:08, that means 7 days, 10 hours, 28 minutes and 08 seconds.

When I opened this table on Visual, the field appears 7.436037037037 and when I changed the data type to Time (there is no Duration type on Visual), this shows 10:28:08, cutting off the 7 days value.

So, I'm thinking to create individuals fields and construct a hierarchy to solve this, but it sounds stupid to me.

There is any other idea?

[Anonymous]

Hi @Anonymous ,

Power bi not support time duration type, you can split the value to different fields to store its units(day, hour, minute, second), it should suitable for deal with time duration values.
In addition, you can also take a look at following blog to know more about these:

Aggregating Duration Time

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks, @Anonymous .

My curiosity is the data type "DURATION" that I found on Query Editor. It is converted to a Decimal Number when I access de data on Visual.

Did you saw it?

Regards.

[Anonymous]

Hi @Anonymous ,

Yes, duration type only works on query table side. It will been forced convert to decimal value when query table convert to data model table.

As I said, you can split it unit to different fields to keep accurate duration values.

Regards,

Xiaoxin Sheng