Forum Discussion

Luiggo07's avatar
Luiggo07
Frequent Visitor
3 years ago
Solved

Time Zone Conversion Issue in Power Query and Power BI

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.RemoveZon...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • collinsg's avatar
    collinsg
    3 years ago

    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.

Rickmaurinus's avatar
Rickmaurinus
Helper V
3 years ago

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.