Time of the last refresh

Two techniques for obtaining the PBI dataset’s last refresh time

Importance of the last refresh time

It is a good practice to include in your Power BI reports when was the last time the dataset got refreshed. There are several techniques for doing so.

Below, you will find two of them, both arriving to the same objective, a table with a single column containing a datetime field that updates every time the dataset refreshes.

World Time API

This is my favorite way of getting the last refresh time, because you do not have to deal with time zones adjustments. Only need to replace “Europe” for the continent we are interested in, and “Zurich” for the city.

let
    Source = Json.Document(
        Web.Contents(
            "http://worldtimeapi.org/api/timezone/",
            [
                RelativePath =
                    "/Europe"&
                    "/Zurich"
            ]
        )
    ),
    datetime = Source[datetime],
    #"Converted to Table" = #table({"Last Refreshed Time"}, {{datetime}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Last Refreshed Time", type datetimezone}})
in
    #"Changed Type"

If you are interested in further diving into the techniques used by this code, I recommend you visit the following blogs:

Chris Webb’s BI Blog: Using The RelativePath

BI Elite: Showing specific timezones

M Query

With a single line of code, we can get the latest UTC time.

let
Source = #table({"Last Refresh"}, {{DateTimeZone.UtcNow()}})
in
Source

The result of this line of M code will be a table with a single row and column showing the UTC time, which is the coordinated universal time.

If we want to display the local time of the machine where this isbeing refreshed, we can also replace "DateTimeZone.UtcNow" for "DateTimeZone.LocalNow" but I would not recommend using this because you can get inconsistent times on what is displayed in the PBI desktop, and in the PBI service. Please see the image below of an example of datasets refreshed almost at the same time.

Oscar Martínez ☁ - Business Intelligence and Financial Planning Consultant - Sereviso / Entech SPA…

@oscarvalerock | Linktree