cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bokbob
Helper I
Helper I

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-A...

 

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.Capture.PNG

 

 

Capture-2.PNG

 

 

Capture-3.PNG

 

 

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft
Microsoft

@Bokbob

You can add a custom column

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

Capture.PNG

View solution in original post

10 REPLIES 10
ArkhangelisK
Regular Visitor

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))

 

 

DaviDamasceno
New Member

Hi i make this video to explain

 

https://youtu.be/3N27eS2oJmA

 

😄

Eric_Zhang
Microsoft
Microsoft

@Bokbob

You can add a custom column

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

Capture.PNG

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.

Hey!

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

 

Thank you!

hi 

 

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

Adjust the formula with -6 as hours in the #duration part:

 

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

 

Specializing in Power Query Formula Language (M)

Hello,

First thanks for all the people posting in this thread it's been a great help.

 

However I've got a timezone issue.   The MySQL server sits on a box that uses UTC (provided by a supplier so no control for me) I am pulling in the data by selecting the table and it comes to sit on a UK environment/users which twice a year has it's timezone change.

 

I'm wondering if there is a way to use this code to adapt based on whether GMT is +1 or not in PowerBI.

 

If not I'll need to figure out how to do it on MySQL and use SQL command to get the data instead. Just preferred to solve it in PowerBi if I can.

 

Thanks

 

Anonymous
Not applicable

Hello,
what digit to place for my timezone New york?

hi 

 

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors