Time conversion (e.g. 2905000 ==> 29m 5s 000ms ==> 0.484722222 H)

[gerard-kouadio]

Hi, I need help please ! 
In my HFSQL database, I have a column name duree_de_prod with, for example, the following values on each line (values on the left).

29m 5s 000ms : 2905000

19m 34s 986ms : 1934986

3m 10s 183ms : 310183

1h 8m 4s : 10804000

1h 27m 9s : 12709000

6s 000ms : 60000

When I import into power bi, it displays the following values (values on the right). I don't understand the system of conversion; BUT I WISH I could convert to HH:MM:SS,  MM:SS:MS, S:MS or simply IN Hour. (Using DAX or Power query) or a SQL script that can do the job and that runs in hfsql ( So I'm going to create a column that will do the conversion in HFSQL directly before importing into Power BI)

[gerard-kouadio · Accepted solution]

Finaly, I found a solution. 

I the numbers with zeros to arrive at a number of 10 characters. and then, I use this DAX formula: 

result_in_H =

VAR TextValue = Table[column]

VAR Hours = VALUE(LEFT(TextValue, 3))

VAR Minutes = VALUE(MID(TextValue, 4, 2))

VAR Milliseconds = VALUE(RIGHT(TextValue, 5))

RETURN Hours + (Minutes / 60) + (Milliseconds / 3600000)

[bhanu_gautam]

@gerard-kouadio Try using

dax
ConvertedDuree =
VAR TotalMilliseconds = your_table_name[duree_de_prod]
VAR Hours = INT(TotalMilliseconds / 3600000)
VAR Minutes = INT(MOD(TotalMilliseconds, 3600000) / 60000)
VAR Seconds = INT(MOD(TotalMilliseconds, 60000) / 1000)
VAR Milliseconds = MOD(TotalMilliseconds, 1000)
RETURN
IF(Hours > 0,
FORMAT(Hours, "0") & "h " & FORMAT(Minutes, "0") & "m " & FORMAT(Seconds, "0") & "s",
IF(Minutes > 0,
FORMAT(Minutes, "0") & "m " & FORMAT(Seconds, "0") & "s " & FORMAT(Milliseconds, "0") & "ms",
FORMAT(Seconds, "0") & "s " & FORMAT(Milliseconds, "0") & "ms"
)
)

[gerard-kouadio]

Sory, but It does'nt work. For example, for 29m 5s 000ms : 2905000, your script send 48m 25s 0ms.