Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I encountered an issue with time zone conversion in Power Query and Power BI, and I would appreciate some guidance from the community. Here are the details:
Formula Used:
DateTimeZone.RemoveZone(DateTimeZone.ToLocal(DateTime.AddZone([Date_UTC], 6)))
Scenario:
Issue:
Request:
Thank you in advance for your assistance and guidance!
Note:
This was ChatGPT's response to my question
"The behavior you described is related to the way Power Query handles time zone conversions and how the data is displayed in different contexts. Here's a breakdown of the process:
Power Query Editor or Power BI Desktop:
Refreshing the dataset in the workspace:
It's important to note that the displayed values may differ depending on the context in which you're viewing the data. This discrepancy occurs due to the different time zone conversion rules being applied in each context.
To ensure consistent results and avoid confusion, it's recommended to set the desired time zone explicitly in your calculations and consider the specific time zone settings in the workspace or the target environment where the data will be consumed.
By explicitly specifying the time zone offset in your formulas and taking into account the time zone settings of the workspace, you can achieve accurate and consistent time zone conversions throughout your data workflow."
Solved! Go to Solution.
Good day @Luiggo07
"ToLocal" depends on the timezone where the query is executed. When executed on your desktop it will be the timezone configured for your desktop. When executed on Power BI Service it will be the timezone your Service sits in (e.g. your Service may be hosted in North America, Western Europe etc). When you first publish to the Service the data will be loaded from your desktop and be the same as was on your desktop but when refreshed in the Service, "Local" will be the local of the Service and so may give a different answer.
If the objective is to remove 6 hours from each datetime then you could use,
= Table.TransformColumns(
#"Previous Step",
{ {"Date-time", each _ -#duration( 0, 6, 0, 0 ) } }
)
where "Previous Step" is the name of your previous step and Date-time is the name of your datetime column.
Hope this helps.
Hey Luiggo,
To achieve the desired daylight savings time, it is recommended that you begin with the UTC time and convert it accordingly. You can follow the instructions provided in this link: https://gorilla.bi/power-query/last-refresh-datetime/
The following logic can be used for this purpose:
let
UTC_DateTimeZone = DateTimeZone.UtcNow(),
UTC_Date = Date.From(UTC_DateTimeZone),
StartSummerTime = Date.StartOfWeek(#date(Date.Year(UTC_Date), 3, 31), Day.Sunday),
StartWinterTime = Date.StartOfWeek(#date(Date.Year(UTC_Date), 10, 31), Day.Sunday),
UTC_Offset = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then 2 else 1,
CET_Timezone = DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset)
in
CET_Timezone
Two crucial elements in this process are:
Ensuring a consistent UTC datetimezone value, regardless of the server's refresh location. More details can be found here: https://powerquery.how/datetimezone-utcnow/
Switching the datetimezone value to the appropriate zone using the method described here: https://powerquery.how/datetimezone-switchzone/
I hope this helps.
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
Thanks @collinsg, I ended up making an alternative solution directly on the file where I get my data from. It seems that this timezone issue may be because of the configuration of my company's computer.
Good day @Luiggo07
"ToLocal" depends on the timezone where the query is executed. When executed on your desktop it will be the timezone configured for your desktop. When executed on Power BI Service it will be the timezone your Service sits in (e.g. your Service may be hosted in North America, Western Europe etc). When you first publish to the Service the data will be loaded from your desktop and be the same as was on your desktop but when refreshed in the Service, "Local" will be the local of the Service and so may give a different answer.
If the objective is to remove 6 hours from each datetime then you could use,
= Table.TransformColumns(
#"Previous Step",
{ {"Date-time", each _ -#duration( 0, 6, 0, 0 ) } }
)
where "Previous Step" is the name of your previous step and Date-time is the name of your datetime column.
Hope this helps.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
33 | |
16 | |
12 | |
11 | |
9 |
User | Count |
---|---|
44 | |
24 | |
16 | |
14 | |
14 |