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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
bvy
Helper V
Helper V

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-xiaoyan-msft
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

5 REPLIES 5
v-xiaoyan-msft
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.

 

The new column is in text format. How can I converted it to datetime format? Thanks

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

@bvy , have you already tried DateTime.AddZone ?

https://docs.microsoft.com/en-us/powerquery-m/datetime-addzone

 

https://community.powerbi.com/t5/Desktop/Convert-utc-to-local-time-zone-using-Power-Query/m-p/45533

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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