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

Time Zone issue

Hello,

 

I have a strange issue with data/time column. I have date/time column that shows correct time in Power Query but when I load data to a table I see time in another time zone (+6:00). I changed my local settings to desired time zone but I still see wrong time zone in my table.

I changed my local settings in Control Panel to be in line with time zone from Power Query.

 

This screenshot shows data in Power Query (this is correct and what I want to present in a table - 9:45:54AM)

pic 1 - PowerQueryEditor.JPG

 

When I load data to a table, this is what I get (3:45:54AM). It is obvious that +6:00 hours are added. How can i solve this?

pic 2 - Table.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
lazzarjovvch74 Helper I
Helper I

Re: Time Zone issue

Yes, I'm 2 hours ahead of GMT (Central European Time), andI want to present data based on EST time (NYC time). What I did so far:

My PC settings is English (United States). Power BI settings is Enhlish (US) too.

settings.JPG

I have "Time" column that shows GMT time (see below):

gmt.JPG

 

Then I added Local Time column in Power Query Edutor that converts GMT from Time column to local time (-4:00). And I got what I wanted in Power Query Editor.

local.JPG

 

But, a problem arises here. When I load Local Time data to a table I see my Central European time in the table column despite my PC settings and Power BI settings are English (US).

So instead of 9:45:54AM as in Power Query Editor, I see 3:45:54PM in my table.

 

I am curious to know why this happen. The local time that I see in Power Query Editor should be loaded to my table if the settings is correct. 

 

In the meantime I solved this by adding another column to a table with a formula, but it would be great if I can do this without additional column.

LocalTime 2 = MyTable[LocalTime] - .25 ---> 0.25 = 6/24 (I am 6 hours ahead of EST).

 

------

 

How I got the local time:

 

=DateTimeZone.ToLocal([start_time])

 

Where [start-time] column shows time from my server (GMT) that is converted to local time (EST).

 

 

View solution in original post

4 REPLIES 4
Microsoft Phil_Seamark
Microsoft

Re: Time Zone issue

What timezone is your PC set to?  What part of the world are you in?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Microsoft Phil_Seamark
Microsoft

Re: Time Zone issue

@lazzarjovvch74

 

I'm guessing you are 2 hours ahead of GMT?  Berlin, Paris???


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Sean Community Champion
Community Champion

Re: Time Zone issue

What function are you using to get the local date and time?

https://msdn.microsoft.com/en-us/query-bi/m/datetimezone-functions

When you publish the report the local function will actually give you the server local time which most likely won't be your time?

Try something like this...

DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), -5 ))

I'm in the Central US Time Zone which right now because of DST is 5 hours behind ( instead of the usual 6 hours )

You can get your value here

https://www.timeanddate.com/

Hope this helps! Smiley Happy

 

lazzarjovvch74 Helper I
Helper I

Re: Time Zone issue

Yes, I'm 2 hours ahead of GMT (Central European Time), andI want to present data based on EST time (NYC time). What I did so far:

My PC settings is English (United States). Power BI settings is Enhlish (US) too.

settings.JPG

I have "Time" column that shows GMT time (see below):

gmt.JPG

 

Then I added Local Time column in Power Query Edutor that converts GMT from Time column to local time (-4:00). And I got what I wanted in Power Query Editor.

local.JPG

 

But, a problem arises here. When I load Local Time data to a table I see my Central European time in the table column despite my PC settings and Power BI settings are English (US).

So instead of 9:45:54AM as in Power Query Editor, I see 3:45:54PM in my table.

 

I am curious to know why this happen. The local time that I see in Power Query Editor should be loaded to my table if the settings is correct. 

 

In the meantime I solved this by adding another column to a table with a formula, but it would be great if I can do this without additional column.

LocalTime 2 = MyTable[LocalTime] - .25 ---> 0.25 = 6/24 (I am 6 hours ahead of EST).

 

------

 

How I got the local time:

 

=DateTimeZone.ToLocal([start_time])

 

Where [start-time] column shows time from my server (GMT) that is converted to local time (EST).

 

 

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors