Forum Discussion
Show column in specific timezone without creating new columns
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.
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.
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.
