how to get the next scheduled refresh date into your power bi report.
introduction.
While there is a standard way on how to show the last refresh time of a dataset in a Power BI report, it appears getting the information on the next/upcoming refresh is a bit trickier. Of course, this depends on how the next refresh is being scheduled. I.e. if your Power BI dataset is refreshed by API calls from other clients, you would need to get the information from the schedule defined there. If the upstream data infrastructure is meta driven, you might be Lucky enough to find the information in the meta specification. Another way could be to fetch the data directly from the code of the ETL or orchestration tool, for example from an Azure DevOps repository.
In this case here, however, we would like to get the information straight from the scheduled refresh feature in the Power BI service by utilising the Get Refresh Schedule API. Since this is where the schedule is defined, this is also the native place to get the information from. I do not recommend hardcoding the same schedule somewhere in a Power BI table as you then need to reactively update it when the schedule itself changes.
prerequisites.
1. Power BI Desktop
2. A way to fetch information from the Power BI Service via REST API. PBI guy has written a great blog post on how to set this up. Please, consider my thoughts about this way in regards to security and governance in the appendix.
1. What’s the goal?
The goal is to end up with a measure that displays the next scheduled refresh in a Power BI report. The data shall be fetched directly from the Power BI service.
2. Load the refresh schedule table
After you have established a way to get hold of data from Power BI via the REST API, load in the refresh schedule information with the Get Refresh Schedule API. In the case below, we used a bearer token, manually fetched via PowerShell with the owning user’s credentials. This bearer token would not take us a long way as it expires rather quickly. I once again refer to the walkthrough article by pbi guy showing a way to programmatically refresh the token. Also, please check out my thoughts on security in the appendix concerning this setup.
Below the Power Query code to fire the API as well as the resulting table:
3. Add new column to the refresh schedule table
In order for the DAX measure in bullet 4 to work properly. we need to add another column weekDay which translates the week days Sunday, Monday, Tuesday etc. to numbers. Note, the week here starts on Sunday which is therefore assigned the number 1.
To add the column, find Add Column in the top bar and press Custom Column. Then, paste the subsequent M Code into the editor. Finally, make sure the columns have the appropriate data types, especially weekDay shall be of type integer.
4. Add the DAX measure and create the visual
As a last step, we add the measure that ultimately shall do the trick. It essentially takes the current time and date and compares it with the schedule from the table fetched in the previous steps. It does so by comparing the weekDay attributes.
Finally, we can use the measure und pull it in a visual. A card visual is likely to be a good candidate.
end.
And here we are! We successfully fetched the master data of a datasets refresh schedule directly from Power BI. As a result we were able to create a measure displaying the next scheduled dataset refresh in a Power BI report. There might be other APIs that you wanna try out to lift your Power BI report to the next level. Lastly, you might also be interested in the article on how to replace several characters in a column in just one power query step, where we utilise a mapping table and a function to scale the replacement of several characters in one go.
appendix.
As already mentioned, in the context of this blog post, I fetched a bearer token via PowerShell. I did it this way as it was easiest for getting started and being able to write the blog post. Of course, this solution won’t bring you far as the token will expire. Then you won’t be able to get the newest refresh schedule data unless you refresh the token again. You could, of course, just do it that way as a one time thing to get the data into Power BI and then disable the refresh itself. However, then your table would become static and a change of the refresh schedule in the service would not automatically be reflected in the dataset. This approach is essentially the same as hardcoding a table with the refresh schedule. Once again, something I would not recommend.
Pbi guy wrote a walkthrough on how you could automatically refresh that token with the help of a service principal. I personally like this approach a lot, but keep in mind that both secret and application id will be visible in the M code (pbi guy makes this clear in the blog post as well). This means anyone that has access to the PBIX file, either because you store it on SharePoint or because one can download it from the Power BI Service is able to get hold of those credentials.
Generally, I follow the pattern to have one service principal per purpose meaning each service principal gets only the permissions that it needs in order to fulfil one specific task. In the case here, this is even more important as the credentials might be exposed to other users.