Include the Last Update Date in a Power Bi Report

Table of Contents

Challenge

Power BI is a core component of one of my recent solution designs. I have used it to combine information from spreadsheets maintained by different users. The combined dataset is then used in a multi-page report.

The challenge was that I needed to display the date and time of the last refresh of the data. This includes the automated refreshes by the Power BI Service. I also needed:

  1. A technique that is replicable across different reports.
  2. Did not rely on complex queries or DAX expressions.
  3. Continues to work without intervention when our timezone changes due to daylight savings.

This is the solution that I came up with, after reading more websites, blog and forum posts than I care to remember.

Solution

Step 1 - TimeZoneDB

Signup for an account at TimeZoneDB. This website provides a very useful API. The Get Time Zone endpoint is the one that is most useful for my use case. In one single API call I can get the current date and time in my timezone.

Step 2 - Add a New Query

Add a new query to the report called LastUpdateDate with the following example code. Replace:

  1. [APIKEY] with your unique API key.
  2. [TIMEZONE with your timezone.
  3. [FIELDNAME] with a descriptive field name.
let
  Source = Xml.Table(Web.Contents("https://api.timezonedb.com/v2/get-time-zone?key=[APIKEY]&format=xml&by=zone&zone=[TIMEZONE]")),
  #"Removed Other Columns" = Table.SelectColumns(Source,{"formatted"}),
  #"Renamed Column" = Table.RenameColumns(#"Removed Other Columns",{{"formatted", "[FIELDNAME]"}}),
  #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"[FIELDNAME]", type datetime}})
in
  #"Changed Type"
An example of the query I’m using in my report

An example of the query I’m using in my report

The result is a table with a single cell. Each time the data in the report refreshes, the current time is stored in the cell in the table. The value is then used in a report to prominently display when the report was last updated.

Benefits

The three main benefits of this approach are:

  1. The solution uses a short and simple Power Query.
  2. The solution continues to work when the timezone changes due to daylight savings.
  3. The date and time of the last update can be displayed prominently on the report.

A downside of this approach is that it relies on an external third party. An alternative approach is required, when this is a concern for your organisation.