Reply
Frequent Visitor
Posts: 14
Registered: ‎10-13-2017
Accepted Solution

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


Accepted Solutions
Frequent Visitor
Posts: 14
Registered: ‎10-13-2017

Re: Time Zone issue

[ Edited ]

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


All Replies
Highlighted
Super User
Posts: 2,618
Registered: ‎11-29-2015

Re: Time Zone issue

[ Edited ]

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!

Super User
Posts: 2,618
Registered: ‎11-29-2015

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!

Super Contributor
Posts: 2,144
Registered: ‎08-11-2015

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

 

Frequent Visitor
Posts: 14
Registered: ‎10-13-2017

Re: Time Zone issue

[ Edited ]

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