Forum Discussion
Report Refresh Time always Wrong
I wasn't sure where to post this so apologies if its in the wrong place.
I have a following Report Refresh time on my report and today it is showing a time that is exactly 2hrs earlier than the correct time.
I am based in London UK, how do I get the time to align?
let
Source = DateTime.LocalNow(),
#"Add 1hr" = DateTime.AddZone(Source,1),
#"Converted to Table" = #table(1, {{#"Add 1hr"}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateTime"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DateTime", type datetime}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "DateTime", "DateTime - Copy"),
#"Extracted Date" = Table.TransformColumns(#"Duplicated Column",{{"DateTime - Copy", DateTime.Date, type date}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Date", "DateTime", "DateTime - Copy.1"),
#"Extracted Time" = Table.TransformColumns(#"Duplicated Column1",{{"DateTime - Copy.1", DateTime.Time, type time}}),
#"Renamed Columns1" = Table.RenameColumns(#"Extracted Time",{{"DateTime - Copy", "Date"}, {"DateTime - Copy.1", "Time"}})
in
#"Renamed Columns1"
I originally added 1 hour before British Summer Time ended in 2025, that was also incorrect, now its 2 hours out.
I'd be grateful for a fix to this because it causes a lot of confusion!
Hello ArchStanton,
Thank you for sharing the screenshot.The error occurs because in Power Query, each line must be added as a new step inside the let in block, with the previous step ending in a comma. If the new line is entered by itself in the formula bar, Power Query tries to evaluate AdjustedDateTime before it exists.
Please update your query like this Add the new step after AsDateTime:
let Source = DateTimeZone.UtcNow(), UKTime = DateTimeZone.SwitchZone(Source, 0, 0), UKAdjusted = DateTimeZone.ToLocal(Source), AsDateTime = DateTimeZone.RemoveZone(UKAdjusted), AdjustedDateTime = AsDateTime + #duration(0, 1, 0, 0), AsTable = #table(1, {{AdjustedDateTime}}), 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 WithTimePower Query steps work sequentially, so the new step must be part of the query and referenced later in AsTable.
Please try the above structure and let me know how it goes.
Best regards,
Ganesh Singamshetty
13 Replies
Try using this instead:
let Source = DateTimeZone.UtcNow(), UKTime = DateTimeZone.SwitchZone( Source, 0, 0 ), UKAdjusted = DateTimeZone.ToLocal( Source ), AsDateTime = DateTimeZone.RemoveZone( UKAdjusted ), 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 WithTimeThanks for this, I've just updated it with your code - lets see what happens.
Last night, after the edit that I made which I mentioned above, the report said it refreshed at 23:55:but the actual refreshed time in service says 02:56 despite me having it scheduled for 01:30:
Scheduled refresh time:
Its such a mess, nothing is aligned, hopefully your fix will work, I'll try it to today.
I've just tested your code and there's still an hour difference, the true refresh time that I can see in Service is 10:01 but the report sows 09:01 so its minus -1hr.
Is there a plus +1hr we can apply somewhere to your code?Hi ArchStanton,
Thank you for posting your query in the Microsoft Fabric Community Forum.Adding a manual +1 hour isn’t a reliable fix here. The underlying issue is that Power BI / Fabric Service evaluates current date‑time functions using UTC/server behavior, and functions like DateTimeZone.ToLocal() don’t necessarily return UK local time (BST/GMT) when they run in the Service.
May be that’s why you’re currently seeing the 1 hour difference. the query result is still based on UTC logic. Manually adding an hour may look correct now, but it will typically become incorrect again when daylight‑saving time changes.
For your reference: Local, fixed, and UTC variants of current time functions - PowerQuery M | Microsoft Learn
- The Service refresh time (10:01) is the actual refresh completion time.
- The report timestamp is being generated separately from query logic.
- A fixed +1 hour offset is only temporary.
Use DateTimeZone.UtcNow() as the base value and apply explicit UK BST/GMT daylight‑saving logic, rather than relying on “local” conversions or hard‑coded offsets.
Best regards,
Ganesh Singamshetty.
By trying different combinations I think I've fixed this - I will see what happens overnight before I close the ticket:
The Power BI Service always works in UTC, and so should you.
please see my response to the other SuperUser
