Time Zone issue

[lazzarjovvch74]

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)

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?

[Sean]

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!

[Phil_Seamark]

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

[Phil_Seamark]

@lazzarjovvch74

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

[lazzarjovvch74 · Accepted solution]

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.

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

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.

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