Power QueryExercises

Time zones in Power Query: DateTimeZone without the confusion

Explainer · By Tahir Nas · Examples run in our simulator · Updated

11 min read

Power Query has two date-and-time types. A datetime is a clock reading with no zone, so 09:00 could be London, New York or UTC. A datetimezone is a clock reading plus an offset from UTC, such as 09:00+01:00, which pins it to one instant. Most time zone bugs come from mixing the two, or from letting the machine that runs the refresh decide what "local" means.

What each type stores

The M language specification describes a datetimezone as "a datetime and a timezone", where the timezone is a number of minutes offset from UTC between -840 and +840 (-14:00 to +14:00) (M language values, Microsoft Learn). A datetime has no offset at all.

TypeExampleWhat it means
datetime2026-07-01T09:00:009am on 1 July, somewhere. The zone is whatever the data's owner meant.
datetimezone2026-07-01T09:00:00+01:009am at UTC+1, which is exactly 08:00 UTC.
datetimezone2026-07-01T08:00:00+00:00The same instant, written in UTC.

An offset is not a time zone. The UK is +00:00 in winter and +01:00 in summer, and a datetimezone value only records which offset applied to that one reading. The rules that decide the offset for other dates live outside M, so you supply them.

Parse timestamps that carry an offset

APIs and log files usually send ISO 8601 text with an offset or a trailing Z for UTC. DateTimeZone.From turns that text into a datetimezone and keeps the offset. For text it follows the parsing rules of DateTimeZone.FromText, whose page shows "2010-12-31T01:30:00-08:00" becoming a value with offset -8 (DateTimeZone.FromText, Microsoft Learn). Because each value knows its offset, sorting puts them in true time order, not text order:

let
    Source = #table(type table [Raw = text], {{"2026-03-29T09:00:00+01:00"}, {"2026-03-29T08:30:00Z"}, {"2026-03-29T04:00:00-05:00"}, {"2026-03-29T13:00:00+05:30"}}),
    Parsed = Table.AddColumn(Source, "Stamp", each DateTimeZone.From([Raw]), type datetimezone),
    Sorted = Table.Sort(Parsed, {{"Stamp", Order.Ascending}})
in
    Sorted

Result

RawStamp
2026-03-29T13:00:00+05:302026-03-29T13:00:00+05:30
2026-03-29T09:00:00+01:002026-03-29T09:00:00+01:00
2026-03-29T08:30:00Z2026-03-29T08:30:00+00:00
2026-03-29T04:00:00-05:002026-03-29T04:00:00-05:00

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The sorted order is 13:00+05:30 (07:30 UTC), 09:00+01:00 (08:00 UTC), 08:30Z (08:30 UTC) and 04:00-05:00 (09:00 UTC). Sorted as text, or as datetime values with the offset thrown away, the 04:00 reading would come first even though it happened last.

Equality works the same way. Two readings of the same instant are equal whatever their offsets:

let
    a = #datetimezone(2026, 3, 29, 9, 0, 0, 1, 0),
    b = #datetimezone(2026, 3, 29, 8, 0, 0, 0, 0)
in
    a = b

Result

true

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The result is true. This is why a datetimezone column is safe to deduplicate or join on when the source mixes offsets, and a datetime column built from the same text is not.

The conversion functions, and what "local" means

M has a small set of functions for moving between offsets. The simulator on this site doesn't support them yet, so these blocks are shown without output. The results in the comments match Microsoft Learn's examples for this input, or follow directly from them (the RemoveZone page uses a different input with the same behaviour).

let
    Stamp = #datetimezone(2010, 12, 31, 11, 56, 2, 7, 30),
    // #datetimezone(2010, 12, 31, 4, 26, 2, 0, 0)
    AsUtc = DateTimeZone.ToUtc(Stamp),
    // #datetimezone(2010, 12, 31, 12, 26, 2, 8, 0)
    AsPlus8 = DateTimeZone.SwitchZone(Stamp, 8),
    // #datetime(2010, 12, 31, 11, 56, 2)
    ClockOnly = DateTimeZone.RemoveZone(Stamp),
    // #datetimezone(2010, 12, 31, 11, 56, 2, 7, 30)
    Tagged = DateTime.AddZone(#datetime(2010, 12, 31, 11, 56, 2), 7, 30),
    // depends on the machine running the query
    AsLocal = DateTimeZone.ToLocal(Stamp)
in
    {AsUtc, AsPlus8, ClockOnly, Tagged, AsLocal}
Shown for Excel and Power BI Desktop only: this code needs a data source or language feature the simulator doesn't run, so no output is shown.

What each one does, from its Microsoft Learn page:

  • DateTimeZone.ToUtc moves the value to offset +00:00, keeping the instant.
  • DateTimeZone.SwitchZone moves it to any offset you name, keeping the instant.
  • DateTimeZone.RemoveZone drops the offset and keeps the clock reading, so the instant is lost.
  • DateTime.AddZone does the opposite: it attaches an offset to a datetime without changing the clock reading. Use it only when you know which offset the source meant.
  • DateTimeZone.ToLocal moves the value to the local zone of the machine evaluating the query. Microsoft's example assumes Pacific time.

That last point is the one that catches people. "Local" in M means the computer running the refresh, not the person reading the report. Converting a datetimezone to date or datetime with Date.From or DateTime.From also goes through local time: the documentation says Date.From returns "the date component of the local datetime equivalent" (Date.From, Microsoft Learn). So a UTC timestamp at 23:30 can land on a different date on a laptop in London in summer than on a server in UTC.

Why the cloud sees a different now

The same rule applies to the current time. Microsoft's page for DateTime.LocalNow explains that Power Query Desktop returns the time on your machine, while "Power Query Online returns UTC time because it's reading the time set on the cloud virtual machines, which are all set to UTC" (DateTime.LocalNow, Microsoft Learn).

So a "today" filter written with DateTime.LocalNow() gives UK users the right day in Excel, but in an online refresh it switches to the new day at midnight UTC. During British Summer Time, that is 1am in the UK. Ask for UTC explicitly, then convert with a rule you control:

let
    NowUtc = DateTimeZone.FixedUtcNow(),
    // same instant everywhere; convert to UK time with the UtcToUk function below
    Today = DateTime.Date(DateTimeZone.RemoveZone(NowUtc))
in
    Today
Shown for Excel and Power BI Desktop only: this code needs a data source or language feature the simulator doesn't run, so no output is shown.

Today here is the UTC date. For the UK date, pass the UTC time through the summer-time function in the next section first. The lesson on refresh in Excel, Power BI Desktop and the service covers where each kind of refresh runs.

Convert UTC to UK time with summer time

A fixed offset can't express "+00:00 in winter, +01:00 in summer", so write the rule. GOV.UK states that the clocks go forward "1 hour at 1am on the last Sunday in March" and back "1 hour at 2am on the last Sunday in October" (When do the clocks change?, GOV.UK). Both changes happen at 01:00 UTC.

let
    LastSunday = (y as number, m as number) as date =>
        let e = Date.EndOfMonth(#date(y, m, 1)) in Date.AddDays(e, -Date.DayOfWeek(e, Day.Sunday)),
    UtcToUk = (utc as datetime) as datetimezone =>
        let
            y = Date.Year(utc),
            bstStart = LastSunday(y, 3) & #time(1, 0, 0),
            bstEnd = LastSunday(y, 10) & #time(1, 0, 0),
            offset = if utc >= bstStart and utc < bstEnd then 1 else 0,
            l = utc + #duration(0, offset, 0, 0)
        in
            #datetimezone(Date.Year(l), Date.Month(l), Date.Day(l), Time.Hour(l), Time.Minute(l), Time.Second(l), offset, 0),
    Source = #table(type table [EventUtc = datetime], {
        {#datetime(2026, 3, 29, 0, 59, 0)},
        {#datetime(2026, 3, 29, 1, 0, 0)},
        {#datetime(2026, 7, 1, 12, 0, 0)},
        {#datetime(2026, 10, 25, 0, 30, 0)},
        {#datetime(2026, 10, 25, 1, 30, 0)}
    }),
    Added = Table.AddColumn(Source, "UkLocal", each UtcToUk([EventUtc]), type datetimezone)
in
    Added

Result

EventUtcUkLocal
2026-03-29T00:59:002026-03-29T00:59:00+00:00
2026-03-29T01:00:002026-03-29T02:00:00+01:00
2026-07-01T12:00:002026-07-01T13:00:00+01:00
2026-10-25T00:30:002026-10-25T01:30:00+01:00
2026-10-25T01:30:002026-10-25T01:30:00+00:00

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The results show both changes in 2026, which GOV.UK lists as 29 March and 25 October:

  • 00:59 UTC on 29 March is still 00:59 GMT (+00:00). One minute later, 01:00 UTC is 02:00 BST (+01:00): the clocks have jumped.
  • Midday UTC on 1 July is 13:00 BST.
  • On 25 October, 00:30 UTC is 01:30 BST and 01:30 UTC is 01:30 GMT. The UK clock reads 01:30 twice that morning, and only the offset tells the two apart.

That October morning, side by side:

UTCUK clockUK offset
00:3001:30+01:00 (BST)
01:0001:00+00:00 (GMT)
01:3001:30+00:00 (GMT)

That repeated hour is the strongest argument for keeping datetimezone (or UTC) in your data. Once you strip the offset, the two readings are identical and the order of events in that hour is lost.

The LastSunday helper uses Date.EndOfMonth and Date.DayOfWeek with Day.Sunday, which returns 0 for a Sunday, so subtracting it walks back to the last Sunday. The Date.EndOfMonth reference has more on month ends. Other countries change on different dates and at different hours, so check their official rules rather than reusing this one.

Local times with a known offset column

Some systems export local times with a separate offset column instead of an ISO string. Subtract the offset to get UTC as a plain datetime:

let
    Source = #table(type table [StoreTime = datetime, OffsetHours = number], {{#datetime(2026, 7, 1, 9, 0, 0), 1}, {#datetime(2026, 7, 1, 9, 0, 0), -4}, {#datetime(2026, 7, 1, 9, 0, 0), 5.5}}),
    Utc = Table.AddColumn(Source, "Utc", each [StoreTime] - #duration(0, 0, [OffsetHours] * 60, 0), type datetime)
in
    Utc

Result

StoreTimeOffsetHoursUtc
2026-07-01T09:00:0012026-07-01T08:00:00
2026-07-01T09:00:00-42026-07-01T13:00:00
2026-07-01T09:00:005.52026-07-01T03:30:00

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

9am at +1 is 08:00 UTC, 9am at -4 is 13:00 UTC, and 9am at +5.5 is 03:30 UTC. The duration is built in minutes so that half-hour offsets such as +05:30 work. If the export has no offset at all, ask the system owner which zone the times are in, and whether that zone observes summer time, before you convert anything.

A working pattern for reports

  1. Parse with the offset. Use DateTimeZone.From on ISO text, or build a datetimezone from a known offset. Unix timestamps are UTC by definition; Convert Unix timestamps and Excel serial dates in Power Query shows how to attach +00:00 as you convert them.
  2. Store and join on UTC. Comparisons and merges are then independent of where the refresh runs.
  3. Derive local columns last, with an explicit rule such as UtcToUk, not DateTimeZone.ToLocal.
  4. Take the date after converting. A sale at 23:30 UTC on 30 June belongs to 1 July in UK summer time. Calculating the date first and converting second gives the wrong day.

Put the local date in your date table relationship, not the UTC date, if the business reports by local days. The guide to building a date table in Power Query shows the table to relate it to, and the date functions cheat sheet lists the other date and time functions. To practise splitting timestamps into parts, try the exercise that asks you to split a timestamp into its date and hour.

The runnable queries above were tested in the Power Query Exercises simulator. The conversion functions shown without output need Excel or Power BI, and their documented results come from Microsoft Learn.

Frequently asked questions

Why did my times shift by an hour after publishing to the Power BI service?

Probably because a step converted to local time, or used DateTime.LocalNow, and Power Query Online reads its time from cloud machines set to UTC. Dataflows run in Power Query Online too, which matters when you move steps from Power BI Desktop into a dataflow. Your laptop in UK summer time is one hour ahead of that. Convert explicitly with a rule you control, as in the UtcToUk example.

Should I change a datetimezone column to datetime?

Only after converting it to the zone you want to report in. Changing the type throws away the offset, so do it deliberately as the last step, never as an automatic "Changed Type" right after import.

Does DateTimeZone know about British Summer Time?

No. A datetimezone stores one offset per value. Functions that use "local" time rely on the operating system's zone rules, which vary by machine, so write the rule yourself when the result must be the same everywhere.

Sources

About the author

Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.

Keep reading

Practise Power Query in the browser with graded exercises checked against fresh data.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.