Search code examples
datetimepowerbiutcpowerbi-datasource

Show LAST REFRESH timestamp in a PUBLISHED report on POWERBI SERVICE

I already checked Stackoverflow for an answer, but I only found question related to showing a timestamp in PowerBI Desktop, which is pretty different from the behaviour in the PowerBI Service, e.g. see

How to display current date and time in power bi visuals?

Visualizing last refresh date in power bi

Why?

  1. I don't want to see in my report the timestamp of the current date and time, since I already have this in the status bar of my operating system.
  2. I don't want to see in my report the timestamp of the last "report" refresh, when only the measures get updated (like in the Service).
  3. I don't want to see the timestamp of last re-import of (most-likely unchanged) data in the Desktop/Service.

What I want to see in my report is the timestamp of the last "dataset" refresh in the Service, which cannot be achived by a measure, but a M-function only!

The problem now is that the Service runs in UTC time, while I'm of cause interested in local time, and all the M-functions to convert a datetimezone number only only accept a fixed time-shift in hours, but do not consider daylight savings.

How would a solution look like to properly overcome this deficit and to show the proper local time of the dataset refresh in a PBI Service report?!

Solution

  • As a workaround I've been pulling the proper local time from worldtimeapi.org so far, see e.g. this PowerQuery M-script:

    let
    Source = Json.Document(
        Web.Contents("http://worldtimeapi.org/api/timezone/Europe/Berlin")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Filtered Rows" = Table.SelectRows(
        #"Converted to Table", each ([Name] = "datetime")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Removed Columns",{{"Value", type datetimezone}}),
    #"Renamed Columns" = Table.RenameColumns(
        #"Changed Type",{{"Value", "Europe/Berlin"}})
in
    #"Renamed Columns"

    However, I just realized that this has become somewhat obsolete meanwhile:

    In the PowerBI Service switch the New Look to ON and then in the title bar next to the report name you get e.g. "Data updated 26/04/20" and in the drop-down menu you can even see the exact update time.