In the past few weeks, a lot of articles pop up about the Power BI Connector in Power Automate, formerly known as Flow. A user-friendly solution that gives you, or your users, the ability to refresh a dataset with a single click from a PowerApp, when an event triggers or even with a click on a button within a Power BI report! A nice solution, without the need to write a single line of code. But, just like the scheduled refreshes configured in the Power BI Service, you’re limited to refresh your data only eight times a day. But what if a user clicks the refresh button eight times in the morning? Or your event suddenly gets triggered more often than you expected? Unfortunately, then you’re out of luck, out of refreshes.
Luckily, there is a solution. Not only for the problem mentioned above, but for all the times when eight refreshes are just not enough. The Power BI connector is a ‘just’ a wrapper around the Power BI REST API, and that is where the solution lies: in this blog post I’ll show you how you can refresh your dataset as often as you like, by making smart use of the API and an Azure Function.
So what’s the magic trick? Instead of refreshing the dataset after eight times, we’re going to download the dataset and publish it under a different name. That way, the ‘refresh counter’ gets reset and we’re able to continue refreshing. After publishing the dataset, we’ll rebind the original report to the newly published dataset, so your user or application won’t notice the change.
By the way, this method isn’t new! Several (desktop) apps can do this. However, this blog I’ll show you the principles, so you can build it your own. And, for free! We will be using Azure Functions to execute the API requests, and up to one million executions, you won’t be charged.
Prerequisites
- Register your app in Azure AD
Make sure you can access the Power BI Rest API. You must register your app within the Azure AD, and get an access code. Microsoft released howto guides for these topics and can be found here. You’ll need steps 1 and 2. - Create an Azure Function
We’ll be programming in C# (.NET Core). A tutorial can be found here. - Install Nuget package Microsoft.PowerBI.Api
We’re not going to write our own API request, there’s a package that can handle this for us. More info.
In the console:
PM> Install-Package Microsoft.PowerBI.Api
Steps
First, we need to initiate a new PowerBI client.
var client = new PowerBIClient(tokenCredentials)
To download the report, we must obtain the groupId and reportId. You can find this id in the URL field of your browser, after opening the report on PowerBI.com.
var group_id = "{YOUR WORKSPACE/GROUP ID}";
var original_report_id = "{YOUR REPORT ID}";
var report_stream =
client.Reports.ExportReportInGroup(groupid, original_report_id);
After downloading the report, we can publish it in the same workspace as the original report. Choose a different name! In the example, I’ve added a datetime stamp, so it’s easy to track when the report (and dataset) was published. Publish the report with the PostImport call.
var import =
await client.Imports.PostImportWithFileAsyncInGroup(group_id, report_stream, {NAME OF YOUR REPORT} + DateTime.Now.ToString(“MMddyyyyHHmmss”));
The upload task might take a while, you can check the status by calling GetImportById, using the id returned from the previous call:
import_status =
await client.Imports.GetImportByIdInGroupAsync(group_id, import.Id)
If the import was succesfull, there will be two new items in the Power BI service: a report and an associated dataset. The next step is to connect the original report to the new dataset. This is done with the rebind call:
var dataset_new = import_status.Datasets[0].Id;client.Reports.RebindReportInGroup(group_id, original_report_id, new RebindReportRequest(dataset_new));
After rebinding, you can trigger the data refresh for the new dataset:
await client.Datasets.RefreshDatasetInGroupAsync(group_id, dataset_new);
At last, the original dataset and the new report can be deleted. Make sure you delete the correct ones!
var delete_new_report =
client.Reports.DeleteReportInGroup(groupid, import_status.Reports.FirstOrDefault<Report>().Id);var delete_old_dataset =
client.Datasets.DeleteDatasetByIdInGroup(group_id, original_report.DatasetId);
That’s it!
Code
Side note: I’m not a professional programmer, so pardon my lack of naming conventions and other best practices. If you have suggestions on how to optimize the code, better async / stream handling, I’m happy to hear from you!
Notes, tips, tricks
- If you have multiple reports that use the dataset, you must rebind them all to the new dataset. You can use GetReportInGroup() to list all the reports in the workspace. Use LINQ to find a specific report (for example, the base report), or to exclude a report (for example, the Usage Statistics):
List<Report>().Where(x => x.Name != "Usage Statistics")
- You can add parameters (for example: reportId ) to the Azure Function, in order to build a generic refresh function.
- Use log.LogInformation() to write to the Azure Function Log output, so you can monitor the execution.
log.LogInformation(DateTime.Now + “: Delete temporary report”);
- You don’t have to download (and publish) the report every time you want to trigger a data refresh; after each upload, you can refresh the dataset eight times. You can check the refresh count with GetRefreshHistoryInGroup(). If you’re out of refreshes, you upload a new dataset, otherwise just refresh the dataset.
var check_refreshes = client.Datasets.GetRefreshHistoryInGroup(groupid, dataset_id);
- You can replace the Power BI Connector in Power Automate with a HTTP request to your function, or you can use a TimerTrigger to trigger the refresh. I’ve added the Function at the end of a Data Factory Pipeline, to trigger the refresh immediately after the new data arrives.
- And, needless to say; the code is not production ready. Please add some security and error handling.
Happy refreshing 😉
Thanks for reading! If you have any complaints or suggestions (both technical and/or about my writing skills), please let me know!