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.
| Type | Example | What it means |
|---|---|---|
datetime | 2026-07-01T09:00:00 | 9am on 1 July, somewhere. The zone is whatever the data's owner meant. |
datetimezone | 2026-07-01T09:00:00+01:00 | 9am at UTC+1, which is exactly 08:00 UTC. |
datetimezone | 2026-07-01T08:00:00+00:00 | The 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
SortedResult
| Raw | Stamp |
|---|---|
| 2026-03-29T13:00:00+05:30 | 2026-03-29T13:00:00+05:30 |
| 2026-03-29T09:00:00+01:00 | 2026-03-29T09:00:00+01:00 |
| 2026-03-29T08:30:00Z | 2026-03-29T08:30:00+00:00 |
| 2026-03-29T04:00:00-05:00 | 2026-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 = bResult
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}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
datetimewithout 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
TodayToday 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
AddedResult
| EventUtc | UkLocal |
|---|---|
| 2026-03-29T00:59:00 | 2026-03-29T00:59:00+00:00 |
| 2026-03-29T01:00:00 | 2026-03-29T02:00:00+01:00 |
| 2026-07-01T12:00:00 | 2026-07-01T13:00:00+01:00 |
| 2026-10-25T00:30:00 | 2026-10-25T01:30:00+01:00 |
| 2026-10-25T01:30:00 | 2026-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:
| UTC | UK clock | UK offset |
|---|---|---|
| 00:30 | 01:30 | +01:00 (BST) |
| 01:00 | 01:00 | +00:00 (GMT) |
| 01:30 | 01: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
UtcResult
| StoreTime | OffsetHours | Utc |
|---|---|---|
| 2026-07-01T09:00:00 | 1 | 2026-07-01T08:00:00 |
| 2026-07-01T09:00:00 | -4 | 2026-07-01T13:00:00 |
| 2026-07-01T09:00:00 | 5.5 | 2026-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
- Parse with the offset. Use
DateTimeZone.Fromon ISO text, or build adatetimezonefrom 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. - Store and join on UTC. Comparisons and merges are then independent of where the refresh runs.
- Derive local columns last, with an explicit rule such as
UtcToUk, notDateTimeZone.ToLocal. - 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
- M language values, Microsoft Learn (learn.microsoft.com)
- DateTimeZone.FromText, Microsoft Learn (learn.microsoft.com)
- DateTimeZone.ToUtc (learn.microsoft.com)
- DateTimeZone.SwitchZone (learn.microsoft.com)
- DateTimeZone.RemoveZone (learn.microsoft.com)
- DateTime.AddZone (learn.microsoft.com)
- DateTimeZone.ToLocal (learn.microsoft.com)
- Date.From, Microsoft Learn (learn.microsoft.com)
- DateTime.LocalNow, Microsoft Learn (learn.microsoft.com)
- When do the clocks change?, GOV.UK (gov.uk)
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
- Incremental refresh in Power BI: what your Power Query must get rightThe Power Query side of incremental refresh: RangeStart and RangeEnd, a half-open date filter, date vs datetime columns, integer date keys and query folding.
- Power Query in Excel for Mac: what works and what doesn'tWhat Power Query in Excel for Mac can do, per Microsoft's documentation and Excel team posts: the editor, data sources, file paths, sharing with Windows.
- Week numbers in Power Query: ISO weeks vs Date.WeekOfYearDate.WeekOfYear restarts at week 1 every 1 January, so it often differs from ISO 8601 weeks. See when and why, and add a tested ISO year and week column in M.
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.