Forum Discussion

susheeltyagi's avatar
susheeltyagi
Icon for Helper I rankHelper I
5 years ago
Solved

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


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

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
    susheeltyagi
    5 years ago

    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.

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      susheeltyagi
      Icon for Helper I rankHelper I
      5 years ago


      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.

  • susheeltyagi's avatar
    susheeltyagi
    Icon for Helper I rankHelper I
    5 years ago

    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.