Dynamic subscription to extract to Excel

[msdf]

Hi Community 

I am in need of extracting report data to excel for multiple customers. 

I have Dynamic subscription set up for another report that extract to PDF for about 300 customers and it runs beautifully. 

Is there anyway we can extract PBI report data to Execel for multiple customers in one go (similar to dynamic subscription)

Thanks

[Shravan133 · Accepted solution]

Currently, Power BI does not have built-in functionality to export data to Excel in the same automated way that it handles PDF exports via subscriptions. However, you can achieve similar results with some workarounds and tools. Here are a few approaches you might consider:

1. Power Automate Integration

Power Automate (formerly Microsoft Flow) can be used to automate data extraction and export tasks. You can set up a flow to extract data from Power BI and save it as an Excel file.

1. Create a Power Automate Flow:

   • Trigger: Use a scheduled trigger or manual trigger.
   • Action: Use the Power BI connector to get data from your dataset.
   • Action: Use the Excel Online (Business) connector to create a file and populate it with data.

   Note: Power Automate flows might have limitations on the volume of data or frequency of execution, so be mindful of these constraints.

2. Set Up a Dataflow:

   • Create a dataflow in Power BI to prepare and structure the data.
   • Use Power Automate to trigger data extraction from this dataflow and export it to Excel.

2. Power BI REST API

The Power BI REST API allows you to programmatically interact with Power BI resources. You can use it to extract data and save it in Excel format.

1. Get Data from Power BI API:

   • Use the Power BI REST API to access the dataset and extract data.
   • You may need to use additional scripts or code to transform this data into Excel format.

2. Automate with Custom Scripts:

   • Write a script (e.g., in Python or PowerShell) that uses the Power BI REST API to extract data and save it as an Excel file.
   • Schedule this script to run at desired intervals using Task Scheduler (Windows) or a similar tool.

3. Paginated Reports

   Paginated Reports in Power BI Report Builder allow for more advanced export options including Excel. If your reports are suitable for paginated formats, you might consider setting up paginated reports and then using subscriptions to export them to Excel.

[msdf]

Hi @Shravan133 

Thank you for your reply. I did follow on your recommendations. 

We had to end up using Python to extract as the dataset is too large for PowerAutomate. Even with the data extract restrictions removed.