Display a refresh schedule tile in power BI service

alang · July 3, 2024, 3:23pm

Publishing a power bi report to the apps that is currently on a refresh schedule. Is there a way to display when the last refresh of the data model occurred on the report by using the refresh schedule time and date.

Keith · July 3, 2024, 3:50pm

Answer:
Hi @alang

Using the DataMentor/EDNA AI tools built in with the EDNA Platform. This is what comes up:

Yes, you can display the last refresh date and time of your Power BI data model directly on your report. Here’s how you can achieve this:
Step-by-Step Guide

Create a Query for Refresh Date:

    Go to Power BI Desktop.

    Select "Transform Data" to open the Power Query Editor.

    Go to "Home" > "Advanced Editor" and paste the following code:

    let
        Source = #table(type table[Last Refreshed=datetime], {{DateTime.LocalNow()}})
    in
        Source

    Click "Close & Apply."

Create a Measure for the Refresh Date:

    In Power BI Desktop, go to the "Model" view.

    Select your new table (it might be named something like Query1 unless you renamed it).

    Create a new measure with the following DAX formula:

    Last Refresh = MAX('Query1'[Last Refreshed])

Add the Last Refresh Measure to Your Report:
    Go back to the "Report" view in Power BI Desktop.
    Insert a card visualization.
    Set the value of the card to the newly created Last Refresh measure.
    Format the card as required to display the date and time clearly.

Publish the Report:
    Save your changes and publish the report to your Power BI Service.
    Now, whenever the data model refreshes, the card will display the latest refresh date and time.

Summary

Using Power Query and DAX, you can create a dynamic display of the last refresh time of your Power BI data model. This ensures users are always aware of the most recent data update.

What methods have you explored for accessing the dataset’s metadata within Power BI? Utilizing DAX functions or Power Query M language functions may offer insights into the last data refresh timestamp.

To access the dataset’s metadata within Power BI, including the last data refresh timestamp, you can use both DAX functions and Power Query M language functions. Here are a few methods to achieve this:

Method 1: Using Power Query M Language

Steps:

Access Power Query Editor:

Open Power BI Desktop.
Go to Home > Transform Data to open the Power Query Editor.

Create a New Query:

In the Power Query Editor, click on Home > New Source > Blank Query.
Rename the query, e.g., RefreshMetadata.

Insert the M Code:

Open Advanced Editor and paste the following code:

let
    Source = #table(type table[LastRefreshed=datetime], {{DateTime.LocalNow()}})
in
    Source

Close and Apply:

Save the query by clicking Close & Apply.

Result:

This query will create a new table with the current date and time, which you can use in your reports to indicate when the data was last refreshed.

Method 2: Using DAX Functions

Steps:

Create a Table in Power BI Desktop:

Go to the Data view and create a new table with the DAX formula:

RefreshTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2020, 1, 1)),
    "Last Updated", NOW()
)

Create a Measure to Access the Last Refresh Timestamp:

Go to the Model view and select the newly created table (RefreshTable).
Create a measure with the DAX formula:

Last Refresh = MAX('RefreshTable'[Last Updated])

Visualize the Last Refresh Timestamp:

Go back to the Report view.
Insert a card visualization.
Set the card value to the Last Refresh measure.

Result:

The card visual will display the last refresh timestamp, which updates whenever you refresh the data in Power BI Desktop.

Summary

Power Query M Language: Provides a static timestamp of when the refresh occurred within the Power Query.
DAX Functions: Provide dynamic refresh timestamps that can be updated and utilized within visuals.

DataMentor/EDNA AI tools are good resource that you can use to help you solve your problems.

I hope this helps.

Thanks,
Keith

alang · July 3, 2024, 5:16pm

Thanks, @Keith . Would this work if using to connect to external data using import mode?

Keith · July 3, 2024, 11:46pm

Hi @alang

Using DataMentor within the EDNA platform that can help you answer your questions.

This what comes up:
This is what i typed in:
Display a refresh schedule tile in power BI service. Would this work if using to connect to external data using import mode?

Yes, you can display a refresh schedule tile in Power BI Service when connecting to external data using Import mode. Here’s how you can do it:

Steps to Display Refresh Schedule:

Open Power BI Service:

Log in to your Power BI online account.

Navigate to the Dataset:

Go to My Workspace or the specific workspace where your dataset is located.
Find the dataset you want to set the refresh schedule for.

Set Up Refresh Schedule:

Click on the ellipsis (...) next to the dataset.
Select Schedule refresh.
Configure the refresh options including frequency, times, and time zone.

Create a Dashboard Tile:

Once the refresh schedule is set, go to your report/dataset and select Pin Live Page or Pin a specific visual to an existing or new dashboard.
This can include information from your dataset, focusing on refresh times if that data is part of your dataset.

Add Information about Refresh Schedule:

While you can’t directly display the data refresh schedule as a tile, you can manually create a custom tile with a text box in the dashboard where you note down the refresh schedule, or use Power BI’s alerts and notifications features.

Limitations:

The refresh schedule tile does not exist explicitly within Power BI; instead, you’ll need to manually create a text box as described.
If you need more dynamic functionality, consider using a custom visual or Power BI API to keep track of refresh schedules programmatically.

Conclusion

While a dedicated refresh schedule tile is not available, you can incorporate refresh schedule information into your dashboards manually or through custom development.

Test it and see what happens that all you can do.

I hope this helps.

thanks
Keith

ankit · July 5, 2024, 3:45am

Hi @alang - Yes, this should work using to connect to external data using import mode. Primarily it will be a separate table that will refresh as part of the dataset and that refresh time can be used to show on report.

Thanks
Ankit J

GuyJohnson · July 5, 2024, 12:18pm

I’ve always used this code which came from Melissa. It has always worked for me.

Melissa’s original post:

To make it easy to find here’s a Power Query M query to generate a “Last refresh” table.

And if you want to give that a try, go to Power Query, select New Source, Blank Query, open the Advanced Editor (select everything) and past this in:

let
Source = DateTime.LocalNow(),
#“Converted to Table” = #table(1, {{Source}}),
#“Renamed Columns” = Table.RenameColumns(#“Converted to Table”, {{“Column1”, “Last Refresh”}}),
#“Changed Type” = Table.TransformColumnTypes(#“Renamed Columns”, {{“Last Refresh”, type datetime}}),
#“Inserted Date” = Table.AddColumn(#“Changed Type”, “Date”, each DateTime.Date([Last Refresh]), type date),
#“Insert Time” = Table.AddColumn(#“Inserted Date”, “Time”, each DateTime.Time([Last Refresh]), type time)
in
#“Insert Time”
.
The result will be a table like this and it will be updated every time the Query (Data model) is refreshed.
image

Let me admit, I created a habit of including a table like this in all my Models and Dataflows and I still find it useful today. Know that you don’t need to create any connections to this table - it’s a supporting table. You can even hide it completely in Report View!

Of course I’m aware of the fact that, with the ‘new look’ enabled, a report refresh date will be shown in the Power BI Service too BUT what if my Dataflow failed and my Report didn’t? Who’s to know?

Another plus, in my opinion, is that you can reference this date instead of using the TODAY() function. Same reason really, if the refresh fails the DAX function doesn’t know or care… That function has only one job and is pretty good at it - you’ve guessed it! - returning today’s date.

So how do you reference this Last Refresh date? Easy, just create a simple measure something like:

Last Refresh Date =
MAX( Last Refresh[Date] )
And now you can reference this [Last Refresh Date] measure instead of using TODAY()

Guy

Keith · July 5, 2024, 3:34pm

Hi @GuyJohnson
thanks for adding the information to help with the issue.

Keith