Forum Discussion
Report Refresh Time always Wrong
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
Hello ArchStanton,
The cleanest place to add the extra hour is after the timezone has been removed and the value becomes a standard datetime.
Specifically, add the following step after AsDateTime:
AdjustedDateTime = AsDateTime + #duration(0, 1, 0, 0)Then use AdjustedDateTime when creating the table instead of AsDateTime.
This will shift the displayed time by +1 hour and should align it with what you are currently seeing in the Service.
Best regards,
Ganesh Singamshetty.
Thanks for getting back to me, I'm not the best with Power Query and I'm getting this error message when I add your step:
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 SingamshettyMy bad, I tried to enter the line in the Query Steps section and not in the Advanced editor window.
Sorry for the confusion!
Thanks again for your help