Create a column in Power BI for last refresh date
Azure DevOps Services | Azure DevOps Server 2020 | Azure DevOps Server 2019
After creating a report and publishing it to Power BI, one of the most common requests is a way to determine the last time the data was refreshed.
Prerequisites
- You must be a member of a project with Basic access or higher. If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
- For Analytics data to be available, the corresponding service must be enabled. For example, to query work tracking data, Boards must be enabled. If it is disabled, Analytics views won't be displayed. To re-enable a service, see Turn an Azure DevOps service on or off
- To use Analytics views, enable the Analytics Views preview feature either for individual users or for the organization.
- Also, you must have your *View Analytics permission set to Allow. For more information, see Grant permissions to access the Analytics service.
- To use Power BI for Azure DevOps or to exercise an OData query for Analytics, you must must have your View Analytics permission set to Allow. By default, all Contributors with Basic access are granted access. To edit shared Analytics views, you must have your *Edit shared Analytics views permission set to Allow. For more information, see Grant permissions to access the Analytics service.
- You must be a member of a project with Basic access or higher. If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
- Verify that Analytics is installed, and if not, then enable it. You must be an account owner or a member of the Project Collection Administrator group to add extensions or enable the service.
- For Analytics data to be available, the corresponding service must be enabled. For example, to query work tracking data, Boards must be enabled. If it is disabled, Analytics views won't be displayed. To re-enable a service, see Turn an Azure DevOps service on or off
- To use Analytics views, enable the Analytics Views preview feature either for individual users or for the organization.
- Also, you must have your *View Analytics permission set to Allow. For more information, see Grant permissions to access the Analytics service.
- To use Power BI for Azure DevOps or to exercise an OData query for Analytics, you must must have your View Analytics permission set to Allow. By default, all Contributors with Basic access are granted access. To edit shared Analytics views, you must have your Edit shared Analytics views permission set to Allow. For more information, see Grant permissions to access the Analytics service.
Add the last refresh date column
To add a column with the last refresh date of the dataset, follow these steps.
Load the Power BI pbix file associated with your view in Power BI Desktop.
In the External Data section of the ribbon, choose Edit Queries.
Open Advanced Editor.
If you haven't already modified the query, you should see text below with specific table values matching your Analytics view.
let Source = VSTS.AnalyticsViews("{OrganizationName}", "{ProjectName}", null), #"{tableid}_Table" = Source{[Id="{tableid}",Kind="Table"]}[Data], in #"{tableid}_Table"Modify the query as follows:
let Source = VSTS.AnalyticsViews("account", "project", null), #"{tableid}_Table" = Source{[Id="{tableid}",Kind="Table"]}[Data], #"Added Refresh Date" = Table.AddColumn(#"{tableid}_Table", "Refresh Date", each DateTimeZone.FixedUtcNow(), type datetimezone) in #"Added Refresh Date"Important
These examples use UTC. You can adjust the query code based on your specific timezone as described in DateTimeZone functions.
When finish, choose Done.
Choose Close & Apply to immediately refresh the dataset.
Identify the Refresh Date column under the field.
Add the field to a card to see the last refresh date on your reports.
