Forum Discussion

admin_xlsior's avatar
admin_xlsior
Post Prodigy
7 years ago
Solved

Direct Query : Transform Integer Value to Time datatype and time format

Hi,

 

I have a fact table which coming from Direct Query, in which it supposed to be an integer contain second (time)

 

Sample as below: (the 1st 2 column is the original value from SQL table, and the rest is just after SQL function Convert which I'm using for checking :

STARTTIMESTOPTIMESTARTTIMESTOPTIME
85105023:38:250:00:00
85105023:38:250:00:00
85105023:38:250:00:00
000:00:000:00:00
000:00:000:00:00
000:00:000:00:00
000:00:000:00:00
000:00:000:00:00
000:00:000:00:00
668306697318:33:5018:36:13
66830018:33:500:00:00
668306697318:33:5018:36:13
670206720018:37:0018:40:00
67020018:37:000:00:00
683296834218:58:4918:59:02
68329018:58:490:00:00
683296834218:58:4918:59:02
670206720018:37:0018:40:00

 

The way I import is not with the last 2 column, so it is just the 1st 2 column, the integer value to Power BI. And in Edit Query, when I change the Data type to Time, it give me error.

 

The last 2 column is the way I want Power BI to display.

 

Please help,

 

Thanks,

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi admin_xlsior ,

     

    You can use following calculate column to achieve your requirement:

    STOPTIME = 
var _hour=INT([STOPTIME]/3600)
var _minute=MOD([STOPTIME],3600)/60
var _second=MOD(MOD([STOPTIME],3600),60)
return
TIME(_hour,_minute,_second)

STARTTIME = 
var _hour=INT([STARTTIME]/3600)
var _minute=MOD([STARTTIME],3600)/60
var _second=MOD(MOD([STARTTIME],3600),60)
return
TIME(_hour,_minute,_second)

    Notice: above formula not works on value who greater than 24 hours.

     

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi admin_xlsior ,

     

    You can use following calculate column to achieve your requirement:

    STOPTIME = 
var _hour=INT([STOPTIME]/3600)
var _minute=MOD([STOPTIME],3600)/60
var _second=MOD(MOD([STOPTIME],3600),60)
return
TIME(_hour,_minute,_second)

STARTTIME = 
var _hour=INT([STARTTIME]/3600)
var _minute=MOD([STARTTIME],3600)/60
var _second=MOD(MOD([STARTTIME],3600),60)
return
TIME(_hour,_minute,_second)

    Notice: above formula not works on value who greater than 24 hours.

     

    Regards,

    Xiaoxin Sheng