DateTimeZone.SwitchZone is a Power Query M function that changes the timezone information of a datetimezone value to a new timezone. The function returns a datetimezone value with the updated timezone information based on the provided hours and minutes.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
DateTimeZone.SwitchZone( dateTimeZone as nullable datetimezone, timezoneHours as number, optional timezoneMinutes as nullable number, ) as nullable datetimezone
The DateTimeZone.SwitchZone function takes a datetimezone value
dateTimeZone and a number of hours
timezoneHours as its inputs. Optionally, it can also take a number of minutes. The function then returns a new datetimezone value, which is identical to the original datetimezone value but with the timezone information adjusted according to the provided hours and minutes.
It’s important to note that if the original datetimezone value does not have a timezone component, the function will throw an exception. This means that you need to ensure that your datetimezone values include timezone information before using this function.
Let’s explore some practical examples to better understand how the DateTimeZone.SwitchZone function works.
Suppose you’re working with a dataset that includes timestamps from different timezones. To standardize your data, you might want to switch all the timestamps to a specific timezone. Here’s how you can do it:
// Output: #datetimezone(2010, 12, 31, 12, 26, 2, 8, 0) DateTimeZone.SwitchZone( #datetimezone( 2010, 12, 31, 11, 56, 02, 7, 30 ), 8 )
This function call will convert the datetimezone value “2023-12-31T11:56:02+07:30” into the datetimezone value “2023-12-31T11:56:02+08:00”, effectively switching the timezone from +07:30 to +08:00.
If you’re working with data that spans the change to or from daylight saving time, you might need to adjust your timestamps by half an hour to account for the change. Here’s how you can use the optional timezoneMinutes argument to do it:
// Output: #datetimezone(2010, 12, 31, 3, 56, 2, 0, 30) DateTimeZone.SwitchZone( #datetimezone( 2010, 12, 31, 11, 56, 02, 2, 0 ), 0, 30 )
Again, this function call will switch the timezone from +02:00 to +01:00, effectively adjusting the timestamp for daylight saving time.
By understanding how to use the DateTimeZone.SwitchZone function, you can effectively manage and manipulate timezone information in your data, have the datetimezone values match your requirements.
Learn more about DateTimeZone.SwitchZone in the following articles:
- Last Refresh DateTime in Power BI (w/ Daylight Savings)
Create a last refresh DateTime in Power BI that respects daylight savings and returns the correct results regardless of the location. Fix location issues! » Read more
Other functions related to DateTimeZone.SwitchZone are: