In my last post I showed how to create a Power Automate custom connector for the new endpoints in the Power BI REST API for exporting a report to a file. In this post I’ll show you how to use this custom connector in a flow in Power Automate.
The three endpoints in the Power BI API that need to be called to export a report (and which have been included in the custom connector from my last post) are as follows:
- Export To File In Group must be called first, to start the process of exporting either a Power BI report or a paginated report to a file. Exporting may take some time, though, so the exported file doesn’t get returned at this point. In the headers of the response there is a retry-after value in seconds telling you how long you must wait initially, before…
- …calling Get Export To File Status In Group to find out whether the export is ready or not. If it isn’t ready you again need to wait the number of seconds specified in the retry-after header before checking the status again. If it is ready, you can then…
- …call Get File Of Export To File In Group to download the export file.
This is all described in the C# example code here, so the challenge is to translate this into a flow. In my case, I want to export the following paginated report to a CSV file:
Here’s what my flow looks like at the top level:
To make things easy to understand I’m using a manual trigger to start the flow and hard-coding the report I want to export. The second Action calls Export To File In Group:
You will need to go into the Settings for this Action and set the Asynchronous Pattern option to Off:
As I said, in the response from this first API call the retry-after header tells you how many seconds you should wait before checking the export status for the first time. This value is used in the next Action, which adds the delay:
Once this delay has passed there’s a Do loop that will call the Get Export To File Status In Group endpoint up to ten times to check if the export is ready:
This call uses the export id found in the response from the call to Export To File In Group:
If this call returns that the status is Running or NotStarted, then it will again wait for the number of seconds specified in the retry-after header; if not then there is no delay:
The expression on the Do loop breaks the loop if the export status is Succeeded or Failed:
@or(
equals(body(‘Call_Export_Status_endpoint_to_check_if_export_is_ready’)?[‘status’], ‘Succeeded’),
equals(body(‘Call_Export_Status_endpoint_to_check_if_export_is_ready’)?[‘status’], ‘Failed’)
)
After the loop, there is a check to see if the export status is Succeeded:
If the export status is Succeeded, then the file is downloaded and saved to OneDrive for Business:
Otherwise, the flow sends a failure notification email:
And here’s the exported CSV file viewed in Excel Online:
And that’s it. I know this flow isn’t as robust as it could be, but I hope it gives you an idea of how to use the custom connector to export Power BI reports in Power Automate; as I said last time, I have very little Power Automate experience so please forgive any newbie mistakes! If you can see a way to improve on what I’ve done here, please leave a comment.
Automated exports from a Power BI paginated report to a CSV file is already very useful but there are other fun things we can do with this – and in my next post I’ll show you another, less obvious example.
Hi, would you be able to export it to excel. we have tried exporting from C# app into excel and PDF but when you open the file you get an error of corrupted file.
Hi Michel,
Sorry for bothering, Could you share you solution from C# how your solution request an export to file functionality
Hi no problem, please let me know how can I share it.
FYI a ticket have been escalate by Microsoft support team to Microsoft product team.
when you export a report with power BI API to excel or PDF the API wrap the file with an object. So when opening the PDF with Edge or chrome it opens but when opening it with adobe reader you get a corrupted file message.
Great stuff as always! I’ve been using a custom connector to the PBI REST API in flow and logic apps for a while and I added these 3 end points to it today. I’m able to test it successfully in the custom connector creation experience and I’m able to successfully execute the calls in Postman but I’m getting the dreaded 403 error in Power Automate and Logic Apps. I’ve waited a few hours because in the past its taken a while for Power Automate to recognize changes to the custom connector but still no progress. This same connector triggers refreshes of the dataset this report uses and has no issues making many other workspace level calls. The workspace is in Premium, it has no sensitivity labels. Any ideas? Any other limitations I’m missing?
Putting the steps on running asynchronously seems to be working for me
Interesting – turning off the asynchronous setting made this work for me
Could you clarify if this can be run with just a Power BI Pro license and not Power BI Premium
You need Premium to do this
Chris, the expression on the Loop until export has succeeded or failed is not working? I get an error saying “fix invalid “expressions for the input parameters of operations
It’s the copied/pasted single quotes in the expression. Delete them and retype them in from:
‘ and ’ to just ‘
Hi everybody,
I am using the exact same expression on the do loop and keep getting an error in my flow checker that says “Fix invalid expressions for the input parameters of operation”
Does anyone know why? I have tried everything
Swap the single quotes to ‘
Flow doesn’t like the formatting if you’re copy/pasting from here
Hi Chris
I was able to make it work, run the flow and get the PDF in my email box.
However, as a next step, I am trying to specify a personal bookmark created on my report in the “defaultbookmark” option of the “ExportToFile” endpoint.
However, it fails with the error –
{
“error”: {
“code”: “BadRequest”,
“message”: “Bad Request”,
“details”: [
{
“message”: “Error converting value \”myBookmarkName\” to type ‘Microsoft.PowerBI.ServiceContracts.Api.V1.PageBookmark’. Path ‘powerBIReportConfiguration.defaultBookmark’, line 1, position 74.”,
“target”: “request.powerBIReportConfiguration.defaultBookmark”
}
]
}
}
Do you know of or have any examples around usage of bookmarks in this ?
Hi Chris, Im a Power BI developer, but getting frustrated in trying to automate an ETL in power Query / Power BI to export to CSV. would the above process be applicable?
Hi Chris, this article was useful.
I am currently trying to export a single Excel file with multiple tabs from Power BI. Any idea if this could work? I am using the logic app, which is similar to Power Automate. I can get it working for pdfs but not EXcel
Hi Chris,
It has worked for me but I can get it working for Pdf not with csv/excel.
Secondly it sends me the same report every time even dataset has been refreshed.
Hi, great post, I’m trying to us this in PowerApps, created the custom connector and in flow the get export file shows be the JSON response with the details, in PowerApps, the same metod when called returns a Bool true or False. Any ideas why this is?