Last refresh shows wrong local time

[Anonymous]

Hi, 

I am using the following query to get the Last Refresh date/time, but I get the (correct datetime) + 1 hour .

let
Source = #table(type table[LastRefresh=datetime],
{{DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),2,0)}}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"LastRefresh", type datetime}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"LastRefresh", type number}}),
#"Added to Column" = Table.TransformColumns(#"Changed Type1", {{"LastRefresh", each _ + 0.08333333, type number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Added to Column",{{"LastRefresh", type datetime}})
in
#"Changed Type2"

[Anonymous]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem.

Best Regards,

Rico Zhou

[Anonymous]

Hi @Anonymous 

I have a test by your Power Query.

I will get 2021/1/15 15:41:37, you say that this datetime is the correct datetime +1 Hour

So the correct datetime is 2021/1/15 14:41:37.

Please try to add 0.041666665 instead of 0.08333333 in your datetime.

let
Source = #table(type table[LastRefresh=datetime],
{{DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),2,0)}}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"LastRefresh", type datetime}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"LastRefresh", type number}}),
#"Added to Column" = Table.TransformColumns(#"Changed Type1", {{"LastRefresh", each _ + 0.041666665, type number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Added to Column",{{"LastRefresh", type datetime}})
in
#"Changed Type2"

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi, 

Thanks for you reply.
Does the new query adjust for the daylight saving time (summer/winter time)?

[Anonymous · Accepted solution]

Hi @Anonymous 

In your query you get refresh date and add 0.08333333 in your datetime.

Then I get a new datetime which equal to my correct time +2.

So 0.08333333 equal to 2 hours, so your correct time is my correct time +1 = datetime +0.08333333/2(0.041666665).

If you want to get the last refresh time, you can try this query.

let
    Source = DateTime.LocalNow(),
    #"Converted to Table" = #table(1, {{Source}})
in
    #"Converted to Table"

For more details about correct last refresh time, please refer to blog and video as below.

Blog: Display Last Refreshed Date in Power BI

Video: How do you get a REFRESH DATE in your Power BI report???

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.