Forum Discussion
Export entire data from query
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
- 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.
- 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).
- Create a Flow in Power Automate with the following steps:
- Save to SharePoint
- Define the SharePoint folder and configure the file name dynamically, for example:
Databricks_Export_@{formatDateTime(utcNow(),'yyyy-MM-dd')}.csv
- Define the SharePoint folder and configure the file name dynamically, for example:
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 🌀.
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.
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.