Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
4 months ago
Solved

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 co...
Need Help
Show and Tell
  • v-ssriganesh's avatar
    v-ssriganesh
    3 months ago

    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

    WithTime

    Power 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

cengizhanarslan's avatar
cengizhanarslan
Super User
4 months ago

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
    WithTime
ArchStanton's avatar
ArchStanton
Power Participant
3 months ago

Thanks 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.