Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
nleuck_101
Resolver III
Resolver III

Last Refresh date/time from Power Service in Power BI Desktop report

Hello All,

 

Is it possible to get the last refresh date/time on the dataset in the service added to a Power BI desktop report? I was trying to use this article https://excelguru.ca/display-last-refreshed-date-in-power-bi/ but the website for the time api is no longer available.

 

Any help would be greatly appreicated!

 

Thanks,

1 ACCEPTED SOLUTION

Could you just create a custom column in Power Query and use:
DateTime.LocalNow()

#"Added Custom" = Table.AddColumn(#"Changed Type", "LastUpdate", each DateTime.LocalNow())

 

Then format it as DateTime

#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"LastUpdate", type datetime}})

 

This should then show wich time the table was refreshed as it will update the column with current date and time.


M code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpMSlaK1YlWMkJiGyOxTWDsWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dummy = _t, Abc = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Dummy", Int64.Type}, {"Abc", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "LastUpdate", each DateTime.LocalNow()),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"LastUpdate", type datetime}})
in
#"Changed Type1"

 

Marius


Br
Marius
BI Fabrikken
www.bifabrikken.no

View solution in original post

13 REPLIES 13
pthapa
Post Patron
Post Patron

Hello all,

I went through most of the advices but I still not sure that the outcome is correct.

No matter either we create a dax (Last Refresh = NOW()) or new query (=DateTime.LocalNow()), both will give same results; however they only reflect the local time.

Our goal is to let the users know when the last dataset was refreshed, not when the visual was last refreshed.

For insatance my dataset refreshes at 6:00am, 12:00pm and 4:00pm. The users will not exactly know that what time the dataset was refreshed. I will tell you the reason because the logic we are using to add the card visual to show the last refresh time will always show the local date/time whenever the user opens the report. So, this date/time is not the same as when the sementic model was refreshed. Does that make sense?

I don't belive that PBI has such feature available to show the actual time of dataset refresh as of now.

Hope PBI team will bring this feature in future updates.

Expert, please add your comments on this, I might be wrong.

Thanks,

pthapa

PunchBird
Helper V
Helper V

First add a blank query in the Power Query Editor

let
    Source = DateTime.Time(DateTime.LocalNow()) & DateTime.Date(DateTime.LocalNow())
in
    Source

Then add this measure to your model and to visualize it put the measure in a card visual:

Last Refresh =
VAR GetLastRefresh =
    FIRSTDATE ( 'LastRefresh'[LastRefresh] )
RETURN
    "Last refreshed on: " & FORMAT ( GetLastRefresh, "dd mmmm yyyy" ) & " "
        & REPLACE (
            FORMAT ( GetLastRefresh, "HH:mm:ss AMPM" ),
            1,
            2,
            HOUR ( GetLastRefresh ) + 2
        )

Please note time is set to UTC+2 (Daylight Savings Time) or UTC+1 - this will display incorrect in Power BI Desktop but correct in Power BI Service (after refresh in Power BI Service). The last parameter in this Measure (+2) needs to be updated when change to/from Daylight Savings Time.

@PunchBird 

This did not work. I try refreshing the dataset in Power BI Service and the time doesn't change.

@mariussve1  is right, you may need to refresh the report in Power BI service as well after refreshing the dataset. 

In the top right corner in service, when you are in the report, you should se this:

mariussve1_0-1662043756296.png

 

Try push the refrehs button I have ringed out in red. This should work, but maybe some cache issues?

Marius


Br
Marius
BI Fabrikken
www.bifabrikken.no
amitchandak
Super User
Super User

@nleuck_101 , The power query code should work. Which time api is no longer available ?

 

Similar solution -https://askgarth.com/blog/add-the-last-refreshed-date-and-time/

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@amitchandak 

That solution only works when you refresh the report manually. I'm looking to grab the scheduled refresh from the dataset in the service and bring it into the desktop report.

Could you just create a custom column in Power Query and use:
DateTime.LocalNow()

#"Added Custom" = Table.AddColumn(#"Changed Type", "LastUpdate", each DateTime.LocalNow())

 

Then format it as DateTime

#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"LastUpdate", type datetime}})

 

This should then show wich time the table was refreshed as it will update the column with current date and time.


M code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpMSlaK1YlWMkJiGyOxTWDsWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dummy = _t, Abc = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Dummy", Int64.Type}, {"Abc", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "LastUpdate", each DateTime.LocalNow()),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"LastUpdate", type datetime}})
in
#"Changed Type1"

 

Marius


Br
Marius
BI Fabrikken
www.bifabrikken.no

I did have to make one change:

#"Added Custom" = Table.AddColumn(#"Changed Type", "LastUpdate", each DateTime.LocalNow() - #duration(0,4,0,0))

@mariussve1 

 

It looks like this just gives me the refresh when I manually refresh the report. Does this give me the refresh from the scheduled refresh on the service?

Yes, this will also update the column when you have a scheduled refresh on the service. The column will get updatet data everytime the table is refreshed.

You might need to adjust it for your timezone, because I think its UTC that is standard in the service. But if you live in UTC+2 then you just add 2 hours:
DateTime.LocalNow() + #duration(0,2,0,0)

Marius


Br
Marius
BI Fabrikken
www.bifabrikken.no

Or do you want to make a own report that grabs all scheduled refresh history from service?
Then you might need to use this rest api:
https://docs.microsoft.com/en-us/rest/api/power-bi/admin/get-refreshables-for-capacity

If you ex use Powert Automate or ADF you can then get all history of refreshes and put it in a table on your datawarehouse or in a csv / excel file.

And then you can use this in your report as source.

Marius


Br
Marius
BI Fabrikken
www.bifabrikken.no

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.