Need help for converting Unix EPOC time to DateTime convertion in Power BI

[susheeltyagi]

Hi

I need the expert help. I am trying to convert the EPOC Unix time to Date Time in Power BI (Advance Editor). I am using the below formula for conversion but I don't know where I am wrong the final datetime have wrong.

I have checked with the Epoch & Unix Timestamp Conversion Online Tools (www.epochconverter.com)

ScrubberTime = 649467810 , 669131925, 650023978 (Unix Timestamp need to convert to DateTime)

=========== ScrubberHex2Dec (New Custom Column ) ==========

=Expression.Evaluate("0x" & [ScrubberTime])

============ScrubberTimeUnformatted (New Custom Column )===========

=#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[ScrubberHex2Dec])

Power BI Advance Editor (Screen shot)

Thanks

Susheel

[amitchandak · Accepted solution]

@susheeltyagi , Try in power query like

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)

New column in DAX

New column 
VAR conversion = 'Table1'[unixtime]/(60*60*24)
RETURN 
Date(19701,1,1)+conversion

[susheeltyagi · Accepted solution]

HI,

I have got mine Mistake . My Unix Time dataType was Text. I have converted to WholeNumber and the below query perfectly converted to the DateTime Correctly. 

New Column =

#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[ScrubberTime])

Thanks for your support.

[amitchandak · Accepted solution]

@susheeltyagi , Try in power query like

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)

New column in DAX

New column 
VAR conversion = 'Table1'[unixtime]/(60*60*24)
RETURN 
Date(19701,1,1)+conversion

[susheeltyagi]

Hi

Thanks for reply. I have tried both the suggestion but in both the case I am getting error.

1.

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [ScrubberTime]/1000)

Output: error in all the rows.

2. DAX = Date(1970,1,1)+(MeasurementQuery[ScrubberTime]/(60*60*24))
Output :
Getting Error while saving.