DateTime.AddZone

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function DateTime.AddZone

The code for the DateTime.AddZone function is written in the M language, which is the primary programming language used by Power Query. In this article, we will explore the M code behind the DateTime.AddZone function and how it works.

Understanding the DateTime.AddZone Function

The DateTime.AddZone function takes three arguments: the datetime value to adjust, the number of hours to add or subtract, and the time zone to adjust for. The function returns a new datetime value that reflects the adjusted time and time zone.

The function works by first converting the datetime value to a UTC value, which is a standardized time format based on the Coordinated Universal Time (UTC) time zone. The function then adds or subtracts the specified number of hours to the UTC value and adjusts for the specified time zone.

Here is an example of how the DateTime.AddZone function works:

DateTime.AddZone(DateTime.LocalNow(), 2, “Pacific Standard Time”)

In this example, the function takes the current local datetime value, adds 2 hours, and adjusts for the Pacific Standard Time time zone. The function returns a new datetime value that reflects the adjusted time and time zone.

The M Code Behind the DateTime.AddZone Function

The M code for the DateTime.AddZone function is relatively straightforward. Here is the code for the function:

(DateTime as datetime, Hours as number, TimeZone as text) as datetime =>

let

UTCValue = DateTime.ToUtc(DateTime),

AdjustedValue = DateTime.AddHours(UTCValue, Hours),

TimeZoneValue = DateTimeZone.FromText(TimeZone),

Result = DateTimeZone.ToLocal(AdjustedValue, TimeZoneValue)

in

Result

Let's break down this code into its individual parts.

Defining the Function Arguments

The first line of the code defines the three arguments for the function: DateTime, Hours, and TimeZone. The DateTime argument is the datetime value to adjust, the Hours argument is the number of hours to add or subtract, and the TimeZone argument is the time zone to adjust for.

(DateTime as datetime, Hours as number, TimeZone as text) as datetime =>

Converting to UTC Time

The next line of code converts the datetime value to a UTC value using the DateTime.ToUtc() function. This is done to standardize the datetime value across time zones.

UTCValue = DateTime.ToUtc(DateTime),

Adding or Subtracting Hours

The third line of code adds or subtracts the specified number of hours from the UTC value using the DateTime.AddHours() function. This effectively adjusts the time value.

AdjustedValue = DateTime.AddHours(UTCValue, Hours),

Adjusting for Time Zone

The fourth line of code converts the specified time zone to a DateTimeZone value using the DateTimeZone.FromText() function.

TimeZoneValue = DateTimeZone.FromText(TimeZone),

Finally, the DateTimeZone.ToLocal() function is used to adjust the adjusted UTC datetime value for the specified time zone and return the final adjusted datetime value.

Result = DateTimeZone.ToLocal(AdjustedValue, TimeZoneValue)

The Power Query M function DateTime.AddZone allows users to adjust datetime values for different time zones by adding or subtracting a specified number of hours. The function works by first converting the datetime value to a UTC value, adjusting the time value, and then converting back to the specified time zone.

The M code behind the DateTime.AddZone function is relatively simple and easy to understand. By understanding how this function works, users can gain a deeper understanding of how to work with datetime values in Power Query and standardize their analyses across different time zones.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)