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

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.