List.DateTimeZones

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.DateTimeZones

In this article, we will explore the M code behind the List.DateTimeZones function and how it can be used to transform and load data.

What is List.DateTimeZones?

List.DateTimeZones is a Power Query M function that returns a list of all time zones. A time zone is a region of the Earth where the same standard time is used. Time zones are important in many applications, including financial and travel-related applications.

The List.DateTimeZones function returns a list of time zones in the format of a table with two columns: “Name” and “Timezone”. The “Name” column contains the name of the time zone, while the “Timezone” column contains the UTC offset of the time zone.

The M Code Behind List.DateTimeZones

The M code behind the List.DateTimeZones function is straightforward. It uses the TimeZoneInfo class in .NET to retrieve all available time zones. The code can be viewed by opening the Advanced Editor in Power Query M and searching for the List.DateTimeZones function.

let

Source = #table(

{“Name”, “Timezone”},

List.Transform(

TimeZoneInfo.GetSystemTimeZones(),

each {

.DisplayName,

Duration.TotalHours(.BaseUtcOffset)

}

)

)

in

Source

The code starts by defining a table with two columns, "Name" and "Timezone". It then uses the List.Transform function to transform the result of the TimeZoneInfo.GetSystemTimeZones function. The List.Transform function applies a function to each element in a list and returns a new list with the results.

The function applied to each element in the TimeZoneInfo.GetSystemTimeZones list returns a two-item list with the display name and the UTC offset of each time zone. The Duration.TotalHours function is used to convert the UTC offset to hours.

The resulting list is then used to create a new table with the same columns as the initial table definition.

Using List.DateTimeZones

The List.DateTimeZones function can be used in many ways within Power Query M. Some examples include:

Filtering time zones by region

Since the List.DateTimeZones function returns all time zones, it can be useful to filter the list based on a specific region. For example, to only display time zones in North America, the following code can be used:

let

Source = List.Select(

List.DateTimeZones(),

each Text.StartsWith([Name], “America/”)

)

in

Source

The List.Select function is used to filter the list based on a condition. In this case, the condition is that the "Name" column starts with "America/".

Converting time zones

The List.DateTimeZones function can be used to convert date and time values from one time zone to another. This can be useful when working with data from different regions or when displaying data in a specific time zone.

For example, to convert a date and time value from the UTC time zone to the Pacific Standard Time zone, the following code can be used:

let

Source = #datetime(2022, 4, 1, 12, 0, 0, 0),

ConvertToPST = DateTimeZone.ToLocal(Source, “Pacific Standard Time”)

in

ConvertToPST

The code starts by defining a datetime value in the UTC time zone. It then uses the DateTimeZone.ToLocal function to convert the datetime value to the Pacific Standard Time zone.

The List.DateTimeZones function is an important function in Power Query M that can be used to work with time zones in various ways. The M code behind the function is straightforward and can be modified to suit specific needs. By mastering this function, data analysts and developers can effectively work with date and time values in Power Query M.

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)