Create a column in Power BI for last refresh date

Azure DevOps Services | Azure DevOps Server 2019

After creating a report and publishing it to Power BI.com one of the most common requests is a way to determine the last time the data was refreshed.

The steps below show how to add a column to your model which will contain the last refresh date of the dataset.

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

  2. In the External Data section of the ribbon, choose Edit Queries.

    Power BI Desktop, Home tab, Edit Queries

  3. Open Advanced Editor.

    Advanced Editor

    If you have not 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.

  4. When finish, choose Done.

  5. Choose Close & Apply to immediately refresh the dataset.

    Power BI Desktop, Home, Close & Apply

  6. Identify the Refresh Date column under the field.

    Power BI Desktop, Fields, Refresh Date field

  7. Add the field to a card to see the last refresh date on your reports.

    Power BI Desktop, Card, Refresh Date Applied