How can I change time zone from UTC to a specific time zone?

[deannag]

Hi, 

I have a series of Event Dates with times and Invoice Dates with times. In power query, I split by the delimiter (T) to get separate columns for dates and times. The Event Date/time is automatically in the local time of the venue, but the invoice date/time is automatically in UTC and I would like to change that to be in the same time zone as the venue of the event. 

I saw that there's DateTimeZone.LocalNow, but I don't want it in my local time zone. I've had the time zone in the same column as the date, and I've also been able to strip the time zone from the time column and keep it in a separate column. Would love any input on the best way to do this. 

Subtracting hours from the time is not an option because of daylight savings, and I need to be more granular than that.

I found this webpage: https://learn.microsoft.com/en-us/powerquery-m/datetimezone-functions but I still need some help on how to use it. 

[Vijay_A_Verma]

As per your description, Invoice Date Time is already in UTC. Let's say you want to offset it by +0730 as per your required timezone

= DateTimeZone.SwitchZone(DateTime.AddZone([Invoice Date Time], 0), 7, 30)

If Invoice Date Time is already in time zone format, then 

= DateTimeZone.SwitchZone([Invoice Date Time], 7, 30)

[deannag]

Will that account for daylight savings as well? 

[Vijay_A_Verma]

Not on its own. This will have to be coded.

Since, every country has different rules for DST, hence I will have to ask you which country is this Venue of the event. If venue of the event is in US, then this rule applies - https://www.nist.gov/pml/time-and-frequency-division/popular-links/daylight-saving-time-dst

If venue of the event is in some other country, then I will appreciate if you can give me a link which provides the rule for DST in this country.

[deannag]

Yes I'm in the US and going from UTC to EST. I'll give this a try on Monday. 

[Vijay_A_Verma]

For US, somebody has already done the hard work. I will not reinvent the wheel. You can pick up DST logic for US from here - https://community.fabric.microsoft.com/t5/Power-Query/Convert-Date-Time-in-UTC-to-Local-Time-with-Da...