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

[bvy]

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

[v-xiaoyan-msft · Accepted solution]

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:

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.

[netghoster]

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

[bvy]

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. 

[v-xiaoyan-msft]

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]

@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