Forum Discussion

Bokbob's avatar
Bokbob
Icon for Helper I rankHelper I
9 years ago
Solved

Converting UNIX time to Date in PowerBI for Desktop

Hi, i am having issues converting the time in Unix time (60*60*24) to date time.

 

i tired http://stackoverflow.com/questions/35650485/how-to-convert-unix-time-to-date-in-powerbi-for-desktop

 

and https://community.powerbi.com/t5/Integrations-with-Files-and/Converting-Unix-timestamp-from-Stripe-API-to-Date-Time/m-p/37519#M3820

 

but i couldnt get it to work. i have the values like the attached in first picture. Please help me convert this number to datetime.

 

 

 

 

 

 

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    Bokbob

    You can add a custom column

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

12 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Bokbob

    You can add a custom column

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for the post, i got it to work for my data.  Only one point was that the unit time I had was unix time in s not ms, so I didn't need the /1000.  I think unix time is quite common in both formats.

    • caito103's avatar
      caito103
      Icon for Helper I rankHelper I
      9 years ago

      Hey!

      And how can i apply this formula but with a specified Time Zone?

       

      Thank you!

      • migue7899's avatar
        migue7899
        New Member
        9 years ago

        hi 

         

        I have the same problem my time zone is gmt -6?

  • ArkhangelisK's avatar
    ArkhangelisK
    Regular Visitor
    5 years ago

    Old thread, but I was able to solve the time zone issue by adding a bit of code to add a GMT timezone and convert that to local time.

     

     

    DateTimeZone.ToLocal(DateTime.AddZone(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [YOUR DATETIME COLUMN]/1000),0))

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    On my side the answer was not working but i figure it out like this :

    let
    lastMonthDate =  Date.AddMonths(DateTime.LocalNow(),-1),
    nbOfSecondSince1970 =  Number.Round(Duration.TotalSeconds(lastMonthDate - #datetime(1970, 1, 1,0,0,0))/1000)
in
    nbOfSecondSince1970