Muokkaa

Jaa

Show last refresh date to Power BI report

  • Artikkeli

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

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.

    Screenshot of Power BI Desktop, Home tab, highlighted Transform Data button in Queries section.

  3. Select Advanced Editor.

    Screenshot of highlighted Advanced Editor button.

    If you didn't modify the query, review the following examples with specific table values matching your Analytics 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"

  1. Modify the query according to the following syntax.

    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"

Note

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

  1. Select Done.

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

    Screenshot of Power BI Desktop, Home, highlighted Close & Apply button.

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

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

    Screenshot of highlighted buttons, Get data, and Blank query.

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

    Screenshot of Power Query Editor, formula for DateTime.LocalNow for Last Refresh Date query.

  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.

    Screenshot of Power Query Editor, To Table option.

    A single column appears with the date.

    Screenshot of converted date column.

    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.

    Screenshot of Transform menu, Change Data Type option to Date/Time.

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

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

    Screenshot of Power BI Desktop, Home, Close & 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.

    Screenshot of Power BI Desktop, Card, Refresh Date Applied.

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.

Screenshot of the Power BI Desktop refresh option.