Earlier this month, I blogged about how “Export to File for Paginated Reports” action seems to be relatively slow (even when compared to doing the export manually from the Power BI Service):
https://www.itaintboring.com/power-automate/export-to-file-for-paginated-reports-execution-time/
So, I thought, maybe an alternative could be to write custom code which would be using Power BI Rest API to do the same, since, after all, this is, probably, exactly the same API that’s utilized in the Power BI connector.
There are 2 API functions I would need to use:
- Export to file in group: https://docs.microsoft.com/en-us/rest/api/power-bi/reports/export-to-file-in-group
- Get export to file status in group: https://docs.microsoft.com/en-us/rest/api/power-bi/reports/get-export-to-file-status-in-group
The second call, once the file is ready, would give me the url I could use to finally download the file.
Quick forward, and, despite all those efforts, it’s still taking 25 seconds to get the file exported programmatically – here is a screenshot from the azure portal for the corresponding Azure Function:
Given that I could probably do it faster if I just opened Power BI Service in the browser, started the report, and used “Export To” there… that’s a little disappointing, of course, since it basically means there is some kind of issue with the API which is preventing it from exporting the reports quickly. I guess that’s not a problem for the long-running reports, but it is for the “print forms”.
However, aside from that, there is still that code, so thought I’d share it anyways.
You will find the source code in git:
https://github.com/ashlega/ItAintBoring.Functions/tree/master/PowerBI
There is an Azure Function there that takes two parameters:
- groupId corresponds to the workspace id in Power BI
- reportId corresponds to the reports id in Power BI
As a result, the function will, unless there is an error, return a docx file which is going to represent exported version of the report. So, for instance, if you wanted to utilize it in the Power Automate, you could use an HTTP action to call the function:
And, then, you could use HTTP action output to, possibly, send generated report by email:
Setting this all up involves a few steps:
1. You will need to register an app in Azure
Interestingly, I did not have to add any permissions:
But you would still need to add a secret:
2. You will need to grant that app access to the Power BI workspace
It can be done by going to the Power BI Service, navigating to the desired Workspace, and, then, using “Access” function to add your app to the workspace (I used “admin” role):
3. You will need to publish the Azure Function from the project in git
Once it’s there, add two configuration parameters (CLIENTID_KEY and SECRET_KEY):
For the values, use application id and secret from the app registration step.
Also make sure to grab function url from the portal for the ExportToFile function in the newly registered function app:
That url is what you can use in the Flows to call that function later
4. You might also need to ensure that Rest API usage is allowed in Power BI
You can do that in the Power BI Admin portal:
As you can see, there are different options there, so you might also use a security group (in which case you’d likely need to add your application principal to that group)
With that done, you should now be able to use that Azure Function to download reports.
Although, again, even if this little project shows the steps required to set everything up for Power BI Rest API usage, yet there is sample (working) code in git, it’s only useful that much. Since, in the end, there is still that slowness when exporting reports to files through the API, and there seem to be nothing I can do about it whether is being done with the help of the out of the box Power BI connector, or whether it’s being done using completely custom code (and an Azure Function in this case).