How to get LastRefresh DateTime in PowerBI Web Page

[SurendraP]

Guys,

I have published report into PowerBi and it's getting refreshed automatically through scheduler and now requester wants to see the last refresh date and time whenever automatic refresh happens, is there anything in powerBI using which we can show this? please suggest on this. 

Note: I am using UTC-08:00 Time zone.  I got something on web(creating blank query and create measures) but after apllying that too datetime deos not show exact one, its showing 7-8 hours ahead time. 

Thank you. 

[vcastello]

Hi @SurendraP

It seems that in the Power BI Service, when you refresh it displays the date and time of the server the dataset is in.

You can find an explanation and the solution in this fab post of @KenPuls

https://www.excelguru.ca/blog/2016/06/08/display-last-refreshed-date-in-power-bi/

Hope that helps.

Vicente

[SurendraP]

Vincente,  In dataset we have option see last refresh date and time but my end users will not be able to see that, i want to show it on my report page using card ( same as posted in @KenPuls page) . I already tried Ken blog but I have same issue as he had with UTC time zone, and second script he given with API is not working now, it says page not found. 

[vcastello · Accepted solution]

Hi @SurendraP

I've seen this explanation by @Virtual_Ames in this forum

https://community.powerbi.com/t5/Desktop/Set-up-a-tile-with-last-refresh-date-and-time/m-p/47250/hig...

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

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

If it works ... don't forget to give him some kudoes. They're deserved

Vicente

[SurendraP]

Thank you @vcastello, provided code worked for me, I just had to change datatype and visuals. I will surely thank @Virtual_Ames for this.