Export Power BI to Excel with Power Automate

Are you looking for a fast and efficient way to export data from Power BI to an Excel file using Power Automate? In this post, I compare and demonstrate two methods that are much faster and more reliable than the traditional method of using an Apply to Each loop.

Method 1: Using Graph API

The first method involves using Graph API. It’s a premium licensed method that invokes an HTTP request to the Graph API. The Graph API requires the site ID, the file ID, and the dynamic range of the data.

Method 2: Using Office Scripts

The second method uses Office Scripts. It requires that Office Scripts is enabled at tenant level and uses the standard Excel Online connecter. It’s a bespoke Typescript Office Script to populate the data. The Office Script accepts parameters such as header, table format, and sheet name and can format the data and turn it into a table.

Table of Contents

• Create a blank Excel File
• Create a nested array of your data
• Populate an Excel file with Graph API
• Populate an Excel file with Office Scripts

Create a blank Excel File

By calling the SharePoint REST API and the Send an HTTP request action, it is possible to create an empty new Excel file. Set the site to your SharePoint site address, the Method to POST and use the following URI which includes the document library path and new file name.

_api/web/GetFolderByServerRelativeUrl('Shared%20Documents')/Files/add(url='ExcelGraphAPI.xlsx',overwrite=true)

Create a nested array of your data

Using your data source, it could be the results of a DAX query or that of a SharePoint List or Dataverse Table, create a nested array using a Select action. Insert a select and enable text mode. Add you data source as the from property and then insert the dynamic values for each of your fields into the array [ ] seperated by commas.

Populate an Excel file with Graph API

For this to work you need to obtain the file ID, site ID. You must also calculate the sheet range, similar to A1: C100, i.e. if there were 3 columns A,B,C and 100 rows 1->100.

To calculate the range, we can insert a Compose with letters A->Z as a string.

Another compose with an expression to chunk the string into an array [“A”,”B”,”C”,……,”Z”] and then select the appropriate column letter based on the length of the first object i.e. row or nested array from our Select, to determine the column numbers. We can use something called an integer index ?[0] to select the first, fourth etc by subtracting 1, as integer indexes start from 0.

chunk(outputs('ComposeColumnLetters'),1)?[sub(length(first(body('Select'))),1)]

Finally in order to get the full range, you can combine the string A1: with the output from the above compose i.e. the letter, in this case D as we have 4 columns and finally the number of rows, which is the length of the nested array in the select action.

To obtain the file ID and site ID you want to Invoke an HTTP request (Premium) to the Graph API. The allows you to retrieve the Site ID dynamically. You could of course obtain this manually and have it as a fixed value but this allows you to make your sites dynamic if required.

https://graph.microsoft.com/v1.0/sites/damobird365.sharepoint.com:/sites/January2024?$select=id

By adding a compose we can get the file ID from the creation of the file via the SharePoint Rest API. As this is not a dynamic value, you will need to write the expression based on the body and then the keys d/UniqueId.

body('Send_an_HTTP_request_to_SharePoint_Create_XLSX')?['d/UniqueId']

By adding another compose you can use an expression to get the Site ID from the body of the HTTP request. It is a comma seperated string and you want the 2nd value from the string. Using integer indexes we can use 1.

Split(outputs('Invoke_an_HTTP_request_SiteID')?['body/id'],',')?[1]

You now have everything in place for the final action to populate the excel file. You will patch the data in an object { } with the output of your select, i.e. the nested array.

Populate an Excel file with Office Scripts

By comparison the Office Script is a lot more simple to setup as the core of the functionality is delivered by the pro code script written in typescript. It’s a single action, which is a standard action – Run Script – from the Excel Online Connector. As per the Graph method, we need a new Excel File (created by the SharePoint REST API call) and the File ID of this new file, obtained using an expression in a compose. Then we can call the Run Script action. The File property is the File ID and the WorksheetData is the nested array.

You’ll note that the worksheet data is a union. This is because the script has the ability to create a table in the excel worksheet. Therefore you have a couple of options to consider when using this script.

If you simply want to pass the output of the select, i.e the data from your source into excel, you can use the output of the select.

If you want to include a header row with your data, you can create another compose action with a nested array and the header column names.

Using the expression union, you can bring together the header and the main nested array.

union(outputs('Compose'),body('Select'))

If you want to include a table in your Excel Worksheet, you can use the parameter Formatastable set to true and if your data includes a header, you can set Includesheader to true, thus ensuring that your new table either includes the provided column names (true) or bumps your data down a row and adds a generic header row (false).

You can download the office script from here 👉 https://damobird365.gumroad.com/l/BulkLoadDataExcelOfficeScript

Comparison and Results

There is no doubt that using either method are considerably much more efficient. The Graph API method does require premium licensing and could be simplified with a custom connector to make this method repeatable and reusable by others. The Excel Office Script is using Typescript which is arguably pro code but the connector is a standard one and the flexibility of the scripts allows you to add a header, configure a table and so much more based on your requirements.