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

[admin_xlsior]

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 :

STARTTIME	STOPTIME	STARTTIME	STOPTIME
85105	0	23:38:25	0:00:00
85105	0	23:38:25	0:00:00
85105	0	23:38:25	0:00:00
0	0	0:00:00	0:00:00
0	0	0:00:00	0:00:00
0	0	0:00:00	0:00:00
0	0	0:00:00	0:00:00
0	0	0:00:00	0:00:00
0	0	0:00:00	0:00:00
66830	66973	18:33:50	18:36:13
66830	0	18:33:50	0:00:00
66830	66973	18:33:50	18:36:13
67020	67200	18:37:00	18:40:00
67020	0	18:37:00	0:00:00
68329	68342	18:58:49	18:59:02
68329	0	18:58:49	0:00:00
68329	68342	18:58:49	18:59:02
67020	67200	18:37:00	18: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,

[v-shex-msft · Accepted solution]

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