Easy consistent Last Refresh Time (your local time zone) solution - no matter how refreshed

[Virtual_Ames]

If you want to make sure your viewers know how fresh the data in a report is - you may want to have a "Last Refreshed" tile showing.  The challenge is - if you publish from your desktop, you get last published in your local time, and if you run a scheduled refresh, you get the last published in the a completely different time zone (local to the server).  This can be confusing to the users of the dashboard.  

One solution is to put this into a consistent and relatable time zone for the viewers - in this example, "Last Refresh" will now be set to "Last Refresh PST". 

To bypass the issues with local time/server time via M - this can be solved using the (yes, pretty awesome) web scraping capability of PowerBI to pull the data from a third party source. 

Below are the steps and code to have a "Last Refresh Tile" that is "Last Refresh PST" (this can be whatever zone you want).

Steps:  

1) Create a new blank query via "Get Data" in PowerBI desktop.

2) Once in the blank query, go to the Advanced Editor

3) Drop in the code below - overwriting the placeholder items in there.  This will give you PST Time - so if you want something else, step through the query and you will see the options in the table for other zones.

let
// get the data from a stable source in table format from the web

    Source = Web.Page(Web.Contents("http://www.timeanddate.com/worldclock/")),

//PowerBI does automatic detection
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),

//navigate to the column that has the time zone that is appropriate for the users of the dashboard (in this case, PST time zone Seattle)

    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column8", "Column9"}),
//filtered the column to just have PST time shown (select whatever you require)

    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Column8] = "Seattle*")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Column9"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Column9", "Last Refresh (PST):"}})
in
    #"Renamed Columns"

4) Close and Load - you will see a query with a column with the current PST time - "Last Refresh (PST)".  

5) Once here, you can tweak your format and visualization - use a table for an easy first step.

6) Once up in your report, to enable automatic refresh, in settings for the dataset in PowerBI, use Anonymous as your credential for the website.

Done - no matter if you refresh from PowerBI desktop or via automatic refresh, it is "Last Refresh PST".

[Virtual_Ames]

To bypass the issues with local time/server time via M - this can be solved using the (yes, pretty awesome) web scraping capability of PowerBI to pull the data from a third party source. 

Below are the steps and code to have a "Last Refresh Tile" that is "Last Refresh PST" (this can be whatever zone you want).

Steps:  

1) Create a new blank query via "Get Data" in PowerBI desktop.

2) Once in the blank query, go to the Advanced Editor

3) Drop in the code below - overwriting the placeholder items in there.  This will give you PST Time - so if you want something else, step through the query and you will see the options in the table for other zones.

let
// get the data from a stable source in table format from the web

    Source = Web.Page(Web.Contents("http://www.timeanddate.com/worldclock/")),

//PowerBI does automatic detection
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),

//navigate to the column that has the time zone that is appropriate for the users of the dashboard (in this case, PST time zone Seattle)

    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column8", "Column9"}),
//filtered the column to just have PST time shown (select whatever you require)

    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Column8] = "Seattle*")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Column9"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Column9", "Last Refresh (PST):"}})
in
    #"Renamed Columns"

4) Close and Load - you will see a query with a column with the current PST time - "Last Refresh (PST)".  

5) Once here, you can tweak your format and visualization - use a table for an easy first step.

6) Once up in your report, to enable automatic refresh, in settings for the dataset in PowerBI, use Anonymous as your credential for the website.

Done - no matter if you refresh from PowerBI desktop or via automatic refresh, it is "Last Refresh PST".

[Rickmaurinus]

As an easy solution, you can adjust for daylight saving time. You start with the UTC time, and adjust the TimeZone offset in the code. 

let
  UTC_DateTimeZone = DateTimeZone.UtcNow(),
  UTC_Date         = Date.From(UTC_DateTimeZone),
  StartSummerTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 3, 31), Day.Sunday),
  StartWinterTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 10, 31), Day.Sunday),
  UTC_Offset       = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then 2 else 1,
  CET_Timezone     = DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset)
in
  CET_Timezone

I explained a bit about these concepts here:

Fix Last Refresh Date/Time in Power BI (Incl Daylight Savings) - Gorilla BI

Enjoy!

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

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

[Anonymous]

This worked like a charm! 👍

[kbuckvol]

This a GREAT solution and gets around published services showing the local time for refreshing.  But I have an issue where it sometimes gives an error "DataFormat.Error: We couldn't parse the input provided as a DateTime value." when trying to convert to time/date.  This is for Chicago, Illinois, USA where we have daylight savings time.  I notice other locations do not have that issue. Is there an extra step we need to add to get around the DST issue? 

[Anonymous]

@Eric_Zhang

Add a custom column in Query Editor. The 8 refers to timezone offset, it always shows the local time of +08:00 timezone.

DateTimeZone.UtcNow()+#duration(0, 8, 0, 0)

This works but how do I modify it to reflect daylight savings time? I need the time stamp to be Pacific, but reflect whether we're in standard time or daylight savings time.

[Eric_Zhang · Accepted solution]

@Virtual_Ames

Thanks for your sharing. That's a awesome idea to indicate a precise refresh time. 🙂

Just some tips, no need to import the web as a source, the Power Query,also known as "M", has a "DateTimeZone.UtcNow()", you can get UTC time with that function and tweak different timezones based on UTC.

[Anonymous]

What should we do with the daylight saving time (DST)?

For example the difference between Melbourne time can be +10, but the other six month it is +11.

Any automatic solution for this?

Thanks

[pkoetzing]

Switching timezones in M-language has a very limited functionallity, as you can switch by a given number of hours only. But this doesn't help when you're located in a daylight savings regime. Pulling the proper timestamp from an external API is unfortunately still the only working solution. I'd recommend using worldtimeapi.org and e.g. the following script:

let
    Source = Json.Document(
        Web.Contents("http://worldtimeapi.org/api/timezone/Europe/Berlin")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Filtered Rows" = Table.SelectRows(
        #"Converted to Table", each ([Name] = "datetime")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Removed Columns",{{"Value", type datetimezone}}),
    #"Renamed Columns" = Table.RenameColumns(
        #"Changed Type",{{"Value", "Europe/Berlin"}})
in
    #"Renamed Columns"

[Virtual_Ames]

Thanks - this is very helpful.

Appreciated.

Brian

[Virtual_Ames]

Excellent.  Thanks.

Question though - let's say I want the timestamp value to always reflect the timezone of the user who is generating the report. Is there a way to set that up using M? That would go one step beyond the current solution we have on the table.

Brian

[Eric_Zhang]

@Virtual_Ames

Add a custom column in Query Editor. The 8 refers to timezone offset, it always shows the local time of +08:00 timezone.

DateTimeZone.UtcNow()+#duration(0, 8, 0, 0)

If it can achieve your goal, please accept it as solution.:) For any question, feel free to let me now.