Last RefreshDate & Time Power Query

The issue is that the code is mixing UTC, local time, and UK daylight-saving time. Adding +1 manually is not reliable because the UK switches between GMT and BST.

If this is Power BI/Fabric and you want the actual UK time when the refresh runs, use the UTC timestamp and explicitly convert it to the UK timezone with daylight-saving handling.

Your current line:

UKAdjusted = DateTimeZone.ToLocal(Source)

is the main problem. ToLocal() uses the local timezone of the Power BI/Fabric environment, which may not be UK time.

Use this instead

let

Source = DateTimeZone.UtcNow(),

// Convert UTC to UK time, automatically accounting for GMT/BST

UKTime = DateTimeZone.SwitchZone(

Source,

if Date.Month(DateTimeZone.RemoveZone(Source)) >= 4

and Date.Month(DateTimeZone.RemoveZone(Source)) <= 10

then 1

else 0

),

AsDateTime = DateTimeZone.RemoveZone(UKTime),

AsTable = #table(1, {{AsDateTime}}),

RenamedCols = Table.RenameColumns(

AsTable,

{{"Column1", "DateTime"}}

),

ChangedType = Table.TransformColumnTypes(

RenamedCols,

{{"DateTime", type datetime}}

),

WithDate = Table.AddColumn(

ChangedType,

"Date",

each DateTime.Date([DateTime]),

type date

),

WithTime = Table.AddColumn(

WithDate,

"Time",

each DateTime.Time([DateTime]),

type time

)

in

WithTime

However, I would not actually recommend that version, because simply checking the month isn't a completely correct way to determine UK daylight saving.

Better solution

If your requirement is simply:

Show the date/time of the Power BI refresh in UK time

then use a timezone-aware conversion rather than manually adding an hour.

In Power Query, a robust approach is to calculate the UK DST period and then apply the appropriate offset.

Also, there's an important point in your example:

Refresh scheduled at 09:00, took 30 minutes, report showed 08:30.

That is not necessarily a timezone problem.

DateTimeZone.UtcNow() / DateTime.LocalNow() in Power BI can behave differently depending on where the query is executed. More importantly, Power Query evaluates UtcNow() when the query is executed, not necessarily at the time you perceive as the scheduled refresh start.

So if the refresh starts at 09:00 and finishes at 09:30, you need to decide what you actually want displayed:

Requirement Timestamp to use

Refresh started at 09:00 Power BI/Fabric refresh metadata

Query executed at 09:30 DateTimeZone.UtcNow()

Dataset finished refreshing at 09:30 Refresh history/API

Current UK time UTC → UK conversion

Your statement that it showed 08:30 instead of 09:30 strongly suggests that you're trying to use Power Query as a "last successful dataset refresh completed at" indicator. If that's the requirement, I wouldn't use UtcNow() at all.

If you just want "Last Refresh" in a Power BI report

A common Power Query pattern is:

let

UTCNow = DateTimeZone.UtcNow(),

UKNow = DateTimeZone.SwitchZone(UTCNow, 1),

LastRefresh = DateTimeZone.RemoveZone(UKNow),

Result = #table(

{"Last Refresh"},

{{LastRefresh}}

)

in

Result

But again, the +1 is only correct during BST. During GMT it will be one hour wrong.

One more important thing

Your original code contains several unnecessary steps:

UKTime

UKAdjusted

AsDateTime

AdjustedDateTime

but UKTime and AdjustedDateTime aren't actually being used to produce your final result.

Your final result is ultimately based on:

AsDateTime = DateTimeZone.RemoveZone(UKAdjusted)

and:

UKAdjusted = DateTimeZone.ToLocal(Source)

So changing +1 elsewhere may have no effect on the value you're ultimately returning.

That's probably why you've been going round in circles.

If you tell me whether this is Power BI Service, Fabric, or Power BI Desktop, and whether you want the refresh start time or completion time, I can give you the exact M code for that scenario

Hi ArchStanton​ -if you're seeing exactly one hour behind, that strongly suggests the timestamp is being interpreted as UTC when you're displaying it as UK local time, or vice versa.

can you try this :

let

UTC = DateTimeZone.UtcNow(),

UK = DateTimeZone.SwitchZone(UTC, 1),

Result = #table(

{"RefreshDateTime"},

{{DateTimeZone.RemoveZone(UK)}}

)

in

Result

or summer/BST, this gives UTC + 1

For winter/GMT, change it to:

let

UTC = DateTimeZone.UtcNow(),

UK = DateTimeZone.SwitchZone(UTC, 0),

Result = #table(

{"RefreshDateTime"},

{{DateTimeZone.RemoveZone(UK)}}

)

in

Result

Dont add +1 static: because that hardcodes the offset and can create problems when the UK switches between GMT and BST.

Hope this helps , please check and confirm.

Hi ArchStanton​ ,

Thank you for the details. The issue is not related to simply adding or removing +1. In Power BI Service, DateTimeZone.ToLocal() can return a different timezone than the UK timezone, and the UK also switches between GMT and BST.

I recommend using the following M code, which takes the current UTC time and explicitly converts it to UK time, including the GMT/BST daylight-saving change. The date is also returned in the required dd/MM/yyyy format.

let Source = DateTimeZone.UtcNow(), Year = Date.Year(DateTimeZone.RemoveZone(Source)), MarchEnd = #date(Year, 3, 31), MarchLastSunday = MarchEnd - #duration(Date.DayOfWeek(MarchEnd, Day.Sunday), 0, 0, 0), OctoberEnd = #date(Year, 10, 31), OctoberLastSunday = OctoberEnd - #duration(Date.DayOfWeek(OctoberEnd, Day.Sunday), 0, 0, 0), CurrentDate = Date.From(DateTimeZone.RemoveZone(Source)), UKOffset = if CurrentDate >= MarchLastSunday and CurrentDate < OctoberLastSunday then 1 else 0, UKDateTimeZone = DateTimeZone.SwitchZone(Source, UKOffset), UKDateTime = DateTimeZone.RemoveZone(UKDateTimeZone), AsTable = #table(1, {{UKDateTime}}), RenamedColumns = Table.RenameColumns( AsTable, {{"Column1", "DateTime"}} ), ChangedType = Table.TransformColumnTypes( RenamedColumns, {{"DateTime", type datetime}} ), WithDate = Table.AddColumn( ChangedType, "Date", each Date.ToText(DateTime.Date([DateTime]), "dd/MM/yyyy"), type text ), WithTime = Table.AddColumn( WithDate, "Time", each Time.ToText(DateTime.Time([DateTime]), "HH:mm:ss"), type text ) in WithTime

Please note that this returns the time at which the Power Query expression is evaluated during the refresh. It does not return the scheduled refresh time. Therefore, if a refresh is scheduled for 09:00 but the query is evaluated at 09:30, the result will be approximately 09:30.

This approach also avoids manually adding +1, since the code handles the UK GMT/BST change automatically.

Thanks,
Chaithanya