CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Hrvoje
Hrvoje

Reputation: 15152

PowerBI - TIME function limitation problem

I have column with number of seconds and I'm trying to convert it to HH:mm:ss format. All is working well if values are not empty or above 32767 since this is limitation of TIME function.

My question is: how can I still convert values above 32767 in HH:mm:ss format?

What I have so far is:

time_elapsed = IF(ISBLANK([time_in_sec]);"NaN";FORMAT(TIME(0;0;[time_in_sec]);"HH:mm:ss"))

But this not even checking because I don't know how to pass empty field as empty field and not Null or "Nan" or anything else when dealing with integer column.

For all other cases function FORMAT(TIME(0;0;[time_in_sec]);"HH:mm:ss") works well.

So 2 problems - how to convert numbers larger than 32767 to HH:mm:ss and how to just pass empty fields. And in case of negative integer number it should return empty field as well.

Upvotes: 3

Views: 1571

Answers (3)

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

FORMAT( DIVIDE( [DurationInSeconds] ) , 86400), "HH:mm:ss" )

This one-liner returns time part. It trims the day part if the DurationInSeconds is larger than 1 day (86400 seconds). If Duration is blank it returns blank.

Upvotes: 1

Hrvoje
Hrvoje

Reputation: 15152

Correct answer from @Nick Krasnov resolved my problem, I only needed to add IF function to regulate appearance of negative numbers, zeros and empty cells. So I used:

hh_mi_ss = if([time_column]>0,(
 VAR hr = FLOOR( [time_column] / 3600, 1)                   // hours
 VAR mn = FLOOR( MOD( [time_column]; 3600) / 60, 1)          // minutes
 VAR ss = FLOOR( MOD ( MOD( [time_column], 3600) ,60 ), 1)  // seconds
RETURN FORMAT( TIME(hr, mn,ss), "HH:mm:ss" ));
"Empty, 0 or negative value")

And in my locale I had to replace , with ; in argument of function.

Upvotes: 1

Nick Krasnov
Nick Krasnov

Reputation: 27251

1) It's possible that space character or another unprintable character may be present. In this case the value isn't considered BLANK. We need to see a sample of your data to tell exactly what's going on.

2) You can implement the formula, that converts seconds to the HH:MI:SS format, yourself, as follows:

  // calculated column
  hh_mi_ss = 
    VAR hr = FLOOR( query[column] / 3600, 1 )                   // hours
    VAR mn = FLOOR( MOD( query[column], 3600) / 60, 1)          // minutes
    VAR ss = FLOOR( MOD ( MOD( query[column], 3600) , 60 ), 1)  // seconds
RETURN FORMAT( TIME(hr, mn,ss), "HH:mm:ss" )

enter image description here

Upvotes: 3

Related Questions