DateTimeZone.SwitchZone

Updated on
Rick de Groot

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

Syntax

DateTimeZone.SwitchZone(
   dateTimeZone as nullable datetimezone,
   timezoneHours as number,
   optional timezoneMinutes as nullable number,
) as nullable datetimezone

Description

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.

Examples

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:

Other functions related to DateTimeZone.SwitchZone are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/datetimezone-switchzone

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy