Time Zone Conversion Issue in Power Query and Power BI

[Luiggo07]

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:

• I have a column named [Date_UTC] containing DateTime values in UTC format.
• My objective is to convert these UTC values to a specific time zone, which in this case is UTC-6.
• I implemented the formula mentioned above to achieve the conversion.

Issue:

• When I execute the formula, the resulting DateTime values are 12 hours behind the original values. For instance, an original DateTime value of "6/29/2023 1:20:23 PM" is converted to "6/29/2023 1:20:23 AM".
• Surprisingly, after uploading the dashboard to my workspace, the displayed date/time in the report remains as "6/29/2023 1:20:23 AM".
• However, upon manually refreshing the dataset directly from the workspace, the date/time values correctly change to "6/29/2023 7:20:23 AM", which is the desired result.

Request:

• I would appreciate any insights on why this discrepancy occurs between the displayed values in Power Query/Power BI Desktop and the refreshed dataset in the workspace.
• How can I ensure consistent and accurate time zone conversion across different stages of the data workflow, including during data exploration and report modification?

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:

1. Power Query Editor or Power BI Desktop:

   • The date/time values are displayed based on the local time zone settings of your machine.
   • When you use the DateTimeZone.ToLocal function to convert the values to local time, the displayed values will reflect the local time zone offset.

2. Refreshing the dataset in the workspace:

   • The date/time values are retrieved from the data source again during the refresh process.
   • The DateTime.AddZone function is applied to perform the time zone conversion based on the specified time zone settings in the workspace.
   • This results in the corrected date/time values according to the desired time zone.

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

[Rickmaurinus]

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:

1. Ensuring a consistent UTC datetimezone value, regardless of the server's refresh location. More details can be found here: https://powerquery.how/datetimezone-utcnow/

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

[Luiggo07]

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.

[collinsg · Accepted 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.