Show last refresh date to Power BI report

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

Adding a last refresh date to a report helps users understand how current the data is. You can display the date and time of the last data update using a card in the report. Regularly refreshing the data models in Power BI ensures that all information is up-to-date.

The steps to add a last refresh date vary based on the source of your Power BI report, whether it's an Analytics view, Power BI, or an OData query.

Note

Several Analytics entity types, such as WorkItemRevision, WorkItem, WorkItemLink, TestRun, and others, include the AnalyticsUpdatedDate property. This property indicates the most recent time that the individual entity references were updated.

Prerequisites

• Access: Be a member of a project with at least Basic access.
• Permissions: By default, project members have permission to query Analytics and create views.
• For more information about other prerequisites regarding service and feature enablement and general data tracking activities, see Permissions and prerequisites to access Analytics.

Add the last refresh date based on an Analytics view

To add a column with the last refresh date of the dataset, do the following steps.

1. Load the Power BI .pbix file associated with your view in Power BI Desktop.

2. In the Queries section of the ribbon, select Transform data > Transform data.

   

3. Select Advanced Editor.

   

   If you didn't modify the query, review the following examples with specific table values matching your Analytics view.

   Private view

   let
       Source = AzureDevOps.AnalyticsViews("{OrganizationName}", "{ProjectName}", []),
       #"Private Views_Folder" = Source{[Id="Private Views",Kind="Folder"]}[Data],
       #"{AnalyticsViewsID_Table}" = #"Private Views_Folder"{[Id="{AnalyticsViewsID}",Kind="Table"]}[Data],
       #"Added Refresh Date" = Table.AddColumn(#"{AnalyticsViewsID_Table}", "Refresh Date", each DateTimeZone.FixedUtcNow(), type datetimezone)
   in
       #"Added Refresh Date"
   

   Shared view

   let
       Source = AzureDevOps.AnalyticsViews("{OrganizationName}", "{ProjectName}", []),
       #"{AnalyticsViewsID_Table}" = Source{[Id="{AnalyticsViewsID}",Kind="Table"]}[Data]
   in
       #"{AnalyticsViewsID_Table}"
   

---

4. Modify the query according to the following syntax.

   Private view

   let
       Source = AzureDevOps.AnalyticsViews("{OrganizationName}", "{ProjectName}", []),
       #"Private Views_Folder" = Source{[Id="Private Views",Kind="Folder"]}[Data],
       #"{AnalyticsViewsID_Table}" = #"Private Views_Folder"{[Id="{AnalyticsViewsID}",Kind="Table"]}[Data],
       #"Added Refresh Date" = Table.AddColumn(#"{AnalyticsViewsID_Table}", "Refresh Date", each DateTimeZone.FixedUtcNow(), type datetimezone)
   in
       #"Added Refresh Date"
   

   Shared view

   let
       Source = AzureDevOps.AnalyticsViews("{OrganizationName}", "{ProjectName}", []),
       #"{AnalyticsViewsID_Table}" = Source{[Id="{AnalyticsViewsID}",Kind="Table"]}[Data],
       #"Added Refresh Date" = Table.AddColumn(#"{AnalyticsViewsID_Table}", "Refresh Date", each DateTimeZone.FixedUtcNow(), type datetimezone)
   in
       #"Added Refresh Date"
   

---

Note

These examples use UTC. You can adjust the query code based on your specific timezone as described in DateTimeZone functions.

5. Select Done.

6. Select Close & Apply to immediately refresh the dataset.

   

Add last refresh date based on a Power BI or OData query

1. From Power BI, select Get data > Blank Query.

   

2. Rename the query to Last Refreshed Date, and then enter the following formula into the function bar.

   

3. To convert the date data to a table format, choose To Table > To Table. Depending on the version you're using, you might need to Invoke parameters first.

   

   A single column appears with the date.

   

   Tip

   If you don't see the To Table option, you can use the following alternative steps to add the last refresh date and time to your reports:

   1. Select the Home tab and select Get Data. Choose Blank Query from the options.
   2. In the Queries pane, right-select on the new query and select Advanced Editor.
   3. Replace the existing code with the following code to create a table with the current date and time:

   let
   Source = #table(
       {"Last Refresh Date"}, 
       {{DateTime.LocalNow()}}
   )
   in
   Source
   

4. From the Transform menu, select the Data Type dropdown menu and select Date/Time option.

   

5. Rename Column1 to something more meaningful, such as Last Refresh Date.

6. From the Home menu, select Close and Apply.

   

Add a card to a report with the Refresh Date

• To add a card with the last refresh date to your reports, under Visualizations, choose Card, and add Refresh Date or Last Refresh Date to Fields.

  

Refresh data

Choose Refresh to refresh report page data and the data model. After all queries are updated, the card refreshes with the latest date.

Related articles

• Learn about Power BI integration
• Create an Analytics view in Azure DevOps
• Create a Power BI report using a default Analytics view
• Publish a Power BI Desktop file to Power BI
• Get started with Power BI Desktop