Export entire data from query

[nish18_1990]

Hi,

I have a custom query with 100 columns in databricks and i need to run the query in power bi and export the entire dataset in excel . How can i automate it in power Bi using power automate. I need to save the files in sharepoint after running the query against the database everyday .

Thanks

Nishika 

[v-priyankata]

Hi @nish18_1990 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@123abc @Zanqueta Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @nish18_1990 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[123abc]

📌 Useful Links & Tutorials

🔗 Guide: Schedule Power BI Data Exports with Power Automate – step-by-step flow building
→ https://www.thebricks.com/resources/guide-how-to-schedule-power-bi-data-exports-to-excel Bricks

🔗 Tutorial: Full Automatic Export Workflow – includes copy template, looping rows
→ https://www.thebricks.com/resources/guide-how-to-schedule-power-bi-data-exports-to-excel-using-ai Bricks

🔗 Microsoft Docs: Export paginated reports via Power Automate
→ https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-automate-paginated-integration Microsoft Learn

Video Tutorial (YouTube):
▶️ Automatic exports of Power BI data — exporting with Power Automate connector YouTub

[123abc]

Following onlie surece will also solve your issue:

Export entire data from query - Microsoft Fabric Community

learn.microsoft.com

Directly Query a Power BI Dataset Using Power Automate and Save it to Excel in SharePoint – TW-Wayte...

Export Data from Databricks to Excel - Coefficient

[123abc]

Hi,

If you need to run a Databricks query every day and automatically export the full results into Excel, Power BI alone can’t do this job. Power BI can refresh data, but it can’t export entire datasets on a schedule, especially when you have many columns or rows. That’s why the automation should be handled directly through Power Automate, which can connect to Databricks, run your SQL query, and then generate the Excel file without limitations.

Export Limitation: Power BI only exports what’s visible in a visual or table (and usually capped at 150,000 rows or fewer).

Scheduled Refresh: Refresh updates the dataset but does not export files.

Power BI REST API: It can retrieve reports or visuals, but not full datasets with 100 columns.

So, if your goal is daily full dataset export to Excel and save to SharePoint, Power BI is not the right tool for the export part.

[Zanqueta]

Hi @nish18_1990 

To automate the export of a large dataset (100 columns) from a custom query in Databricks, executed through Power BI, and save the file to SharePoint on a daily basis, you can follow this recommended approach:

Recommended Strategy

1. Connection Mode
   • Use Import mode in Power BI to load the data from the Databricks query.
   • Configure a Scheduled Refresh in the Power BI Service to ensure the dataset is updated daily.
2. Automated Export to Excel via Power Automate
   • Create a Flow in Power Automate with the following steps:
     • Trigger: “When a dataset is refreshed” (Power BI).
     • Action 1: “Run a DAX query” (Power BI).
     • Action 2: “Create file” in SharePoint, using the exported content.
   • Recommended format: CSV (lighter and more scalable than XLSX for large datasets).
3. Save to SharePoint
   • Define the SharePoint folder and configure the file name dynamically, for example:
     Databricks_Export_@{formatDateTime(utcNow(),'yyyy-MM-dd')}.csv

Please, look:
Power Automate Run A Query Against A Power BI Dataset

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.