PowerBI - TIME function limitation problem

Question

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.

Answer 1

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" )