Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
deannag
Helper I
Helper I

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

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. 

5 REPLIES 5
Vijay_A_Verma
Super User
Super User

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)

Will that account for daylight savings as well? 

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.

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors