Show column in specific timezone without creating new columns

[JKoivu]

I've been struggling with displaying UTC timestamps in the client timezone. I've searched for answers online, but most of them are either very complex or require new columns for each timestamp. My ideal solution would be to somehow transform the column value in Power Query by adding the timezone offset (ZoneHours) to the time. But while researching this, I got the impression that this would not work in Power BI service...

So basically I have the following questions:

• I have set the column type to datetimezone, and it's looking right in Power Query editor. For example. `09/04/2022 4.00.48 +03:00`. However, when I put this data in a table it still shows 4.00.48, I expected 7.00.48. I then tried adding a step to calculate local time, this didn't affect anything. Why?
• The report is consumed through Power BI service. Is the time zone information present there? If not, how can I add hours to the time while taking daylight savings into account?

I'm actually quite surprised honestly, as I though that this would be pretty trivial thing to do, yet I've wasted many hours already with basically no results...

[v-jingzhang · Accepted solution]

Hi @JKoivu 

You may refer to this document Data types in Power BI Desktop 

Date/Time/Timezone data type is not present in the data model. The Date/Time/Timezone values are converted into Date/Time when loaded into the model. The Power BI model doesn't adjust the timezone based on a user's location or locale etc. If a value of 09:00 is loaded into the model in the USA, it will display as 09:00 wherever the report is opened or viewed.

When you set the column type to datetimezone, it uses the local timezone on your computer. However, in Power BI Service, it uses the service time while the service time is always UTC time. As a result it will not return the same result as that in Power BI Desktop on your computer. 

If you want to add the offset hour to UTC times in Power Query Editor, you can add a step with below M code. This adds the fixed 3 hours without taking daylight savings into account. 

= Table.TransformColumns(#"Previous Step", {"Column1", each _ + #duration(0,3,0,0)})

If your data doesn't cross multiple years, perhaps you can identify the date on every row and compare it with the daylight savings period start/end dates. According to which period it is in, add different duration values to the datetime value. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[JKoivu]

Thank you for the extensive answer!

So it was as I feared, no "out of the box" solution for handling timezones with DST. Not ideal but I'll work something out. Thanks again for your help.