cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bvy
Helper III
Helper III

Power Query - Convert UTC to User's Client Local Time?

I have a Power BI table with a datetime field in UTC. I simply want a new column in that table with that datetime converted to the time zone of the user's client. I'm using Power Query and see several ways to add and manipulate time columns, but how do I get the time zone offset from the user's client ? Thank you. .

 

1 ACCEPTED SOLUTION
v-caitlyn-mstf
Community Support
Community Support

Hi @bvy

 

You can use ‘DateTimeZone.ToLocal’ Function in query editor to meet your need.

As you mentioned, you have a Power BI table with a date time field in UTC. Here is a sample and you can follow these steps:

Open Query Editor> under the Add column tab> click Add Custom Column> Under the formula part> copy and paste the following:

 

DateTimeZone.ToLocal([Original Time])

 

Finally, you will get a result like this:

vcaitlynmstf_0-1628761894933.png

 

 

For more information, please see: DateTimeZone.ToLocal

By the way, 'DateTimeZone.ToLocal' function is not supported in Direct Query mode currently.

 

As a workaround, you can also realize your needs by using ‘DateTime.AddZone’ function.

This function adds the time zone hours as an offset to the input date time value and returns a new date time zone value.

For detailed operation steps, you can refer to this case: Convert UTC to client time zones

 

Hope it helps.

 

Best Regards,

Caitlyn Yan

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

4 REPLIES 4
v-caitlyn-mstf
Community Support
Community Support

Hi @bvy

 

You can use ‘DateTimeZone.ToLocal’ Function in query editor to meet your need.

As you mentioned, you have a Power BI table with a date time field in UTC. Here is a sample and you can follow these steps:

Open Query Editor> under the Add column tab> click Add Custom Column> Under the formula part> copy and paste the following:

 

DateTimeZone.ToLocal([Original Time])

 

Finally, you will get a result like this:

vcaitlynmstf_0-1628761894933.png

 

 

For more information, please see: DateTimeZone.ToLocal

By the way, 'DateTimeZone.ToLocal' function is not supported in Direct Query mode currently.

 

As a workaround, you can also realize your needs by using ‘DateTime.AddZone’ function.

This function adds the time zone hours as an offset to the input date time value and returns a new date time zone value.

For detailed operation steps, you can refer to this case: Convert UTC to client time zones

 

Hope it helps.

 

Best Regards,

Caitlyn Yan

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Thank you, that works a little better. Now just need to confirm what "Local" means in this context -- the location of the user, or the Azure region/availability zone where the dataset is hosted. 

Hi@bvy

 

It‘s the location of the local computer.

It works in Power BI Desktop because it takes the offset of the time zone from your operating system.

But in Power BI Service, the Azure cloud runs using UTC only. It means, a UTC timestamp stays unchanged if you refresh your data in the cloud.

 

 

Hope it helps.

 

Best Regards,

Caitlyn Yan

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.