Power BI Automation with Pro licence

[pravallikag]

Hi-

I have a Power BI Pro license, I need to automatically export a table to Excel every morning at 6 AM with refreshed data, without any manual steps. I tried using 'Analyze in Excel', but since we don’t have a Microsoft 365 Business license, that option isn’t available. Is there any other way to automate this process? Any guidance would be greatly appreciated.

[Poojara_D12]

Hi @pravallikag 

Since you have a Power BI Pro license and need to automatically export a table to Excel every morning at 6 AM with refreshed data, but don’t have access to “Analyze in Excel” due to the lack of a Microsoft 365 Business license, you’ll need to consider alternative automation options outside of that feature. One practical approach is to use Power Automate, which integrates with Power BI and can trigger actions based on dataset refresh or on a set schedule (e.g., 6 AM daily). However, Power Automate cannot directly export a visual or table to Excel unless you combine it with additional logic—for example, pulling the data via the Power BI REST API, storing it in a dataflow or intermediate store (like SharePoint or OneDrive), and then writing it into an Excel file using Power Automate’s Excel Online (Business) connectors. This would typically require a flow that first queries the data from Power BI (e.g., using a paginated report or Dataflow API), transforms it if needed, and writes it to a spreadsheet.

Alternatively, if your data resides in a SQL Server, Azure SQL, or other database, you can bypass Power BI entirely and use Power Automate or an Azure Data Factory pipeline to extract the required table directly and dump it into Excel on a schedule. If you're limited by licensing or connectors, another fallback is to use a custom PowerShell or Python script that authenticates using a service principal or credentials, queries the Power BI REST API or the source system, and generates an Excel file—then scheduled via Windows Task Scheduler or Azure Automation. While these workarounds involve some setup effort, they allow full automation without requiring user interaction or higher-tier licenses.

[v-tsaipranay]

Hi @pravallikag ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @pravallikag ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay]

Hi @pravallikag ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Also thank you @Cookistador  for your valuable inputs.

Thank you.

[Cookistador]

Hi @pravallikag 

Do you have power automate licence ? 

Normally you should

The approach is pretty easy

1. In your power bi report (in desktop), Optimize ->  Performance Analyzer
   In the new pane, click on start recording and refresh visual, then you click on Copy query 
   
2. In power automate, you can create a schedule flow, 

   In the step, run a query against a dataset, you select the worksapce in which the reports is saved and then you pasted the query you copied in the previous step

3. After that, you have to create a csv to map the field with

   here, it is the tricky part is to make the mapping, to do that, you have to use the following formula instead of entry value

   item()?['[your fieldname]']

   Once you will save, the formula should appear as a power bi item

   
4. Then create a csv table using the output of the previous step

Do not hesistate to ask if you need more help 🙂

[v-tsaipranay · Accepted solution]

Hi @pravallikag  ,

Thank you for reaching out to the Microsoft Fabric Community. 

Currently, with a Power BI Pro license, there is no built-in way to fully automate the export of report visuals or tables to Excel. The "Analyze in Excel" feature which lets you connect an Excel file directly to a Power BI dataset and work with the data live requires a Microsoft 365 Business license or higher. Since that’s not available in your setup, this option won’t work for you. (Create Excel workbooks with refreshable Power BI data – Microsoft Learn)

While Power Automate is a powerful tool for creating scheduled workflows, it doesn’t support exporting visuals or tables directly from a Power BI report. However, if your report is built on an external data source such as SQL Server, SharePoint, Excel files, or Dataverse you could create a Power Automate flow to pull that data directly from the source and write it to Excel on a schedule. This means Power BI isn't involved in the export, but you still achieve automation.

Another option you could look into is using the Power BI REST API. The Export to File API lets you export full report pages as PDF or PowerPoint files (but not Excel) and requires some setup like registering an app and getting admin consent to use a service principal. This route involves writing a script (in PowerShell, Python, etc.) and running it on a schedule using something like Azure Automation or Windows Task Scheduler. (Export To File in Group – Power BI REST API)

If your goal is to truly automate exporting tabular data from a Power BI report into Excel, you’d need to upgrade to Power BI Premium Per User (PPU) or move to a Fabric Capacity environment. These support Paginated Reports, which are designed for highly formatted reports and can be scheduled to export directly to Excel with full control over layout and formatting. (What are paginated reports in Power BI? – Microsoft Learn)

I hope this gives you an idea, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.