Calling The Power BI Export API From Power Automate, Part 2: Creating A Flow That Exports A Paginated Report To A CSV File

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:

Paginated report

Here’s what my flow looks like at the top level:

High level view

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:

Start export

You will need to go into the Settings for this Action and set the Asynchronous Pattern option to Off:

AynchOff

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:

First 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:

Do loop

This call uses the export id found in the response from the call to Export To File In Group:

Check status

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:

Second 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:

Last condition

If the export status is Succeeded, then the file is downloaded and saved to OneDrive for Business:

Save file

Otherwise, the flow sends a failure notification email:

Failure email

And here’s the exported CSV file viewed in Excel Online:

Excel

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.

14 responses

  1. 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.

  2. Pingback: Chris Webb's BI Blog: Calling The Power BI Export API From Power Automate, Part 1: Creating A Custom Connector Chris Webb's BI Blog

  3. 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?

  4. Could you clarify if this can be run with just a Power BI Pro license and not Power BI Premium

  5. 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

  6. 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

  7. 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 ?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: