Forum Discussion

Post Prodigy

1 year ago

Solved

Excel Power Query Refresh

Hi, Apologies if this is the incorrect section. I'm in excel and going into Get Data > Power BI and using my spreadsheet via a dataset and Power Query. This is working fine, but i have about ...

Need Help

PwerQueryKees
1 year ago
If I understand you correctly, you are using powerbi to get the data from the database, but you want to acces it through Excel. You are not using the PowerBI for reporting or dashboards.

If that is the case, skip PowerBI and make a seperate DB Connection spreadsheet instead. Then connect the user's spreadsheet(s) to this new spreadsheet.

If you place the DB Connect spreadsheet on a sharepoint site of a network folder, you can connect the user's spreadsheet to the DB connect spreadsheet.

I have been using this approach for performace reason, putting all complicated PowerQuery in the DB Connect spreadheet and connect user's spreadsheet to the DB Connect sheet. 2 advantages: Only 1 place to maintain the complex PowerQuery and speed.

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.
v-dineshya
1 year ago
Hi lherbert501 ,

Please refer below links.

Publish a paginated report to the Power BI service - Power BI | Microsoft Learn

Export Power BI paginated reports - Power BI | Microsoft Learn

Paginated reports in Power BI: FAQ - Power BI | Microsoft Learn

Export Power BI paginated report to Microsoft Excel (Power BI Report Builder) - Power BI | Microsoft Learn

Develop Paginated Report using Power BI Report Bui... - Microsoft Fabric Community

Solved: How to export more than 150000 rows from Power BI ... - Microsoft Fabric Community

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

v-dineshya

Community Support

1 year ago

Hi lherbert501 ,

Thank you for reaching out to the Microsoft Community Forum.

Hi jaineshp , Thank you for your prompt response.

Hi lherbert501 , In addition to jaineshp response, I am adding some more points.

Please try below alternative workarounds.

1. Paginated Reports are designed for handling large datasets and exporting them to Excel without row limits. You can publish a paginated report to Power BI and allow users to export the full dataset to Excel.

Note: This avoids the 500k row limit and doesn’t require database access.

2. Create a Power BI Dataflow that stores your data in a centralized location. Then use Excel’s Power Query to connect to the Dataflow via the OData feed.

Note: This can bypass the 500k row limit depending on how the data is queried.

3. In Power BI Service, you can export the full dataset to Excel or CSV using the “Export Data” option from a visual. You can create a table visual with all 523k rows and allow users to export from there.

Note: Export limit is 150k rows for visuals unless using paginated reports.

4. If you are using Azure, you can store the full dataset in Azure Data Lake or Synapse, and connect Excel directly to it using Power Query.

Note: This avoids Power BI limits and gives full control over data refresh and access.

5. Split the dataset into chunks and loading them separately into Excel. You can use Power BI measures or filters to allow users to select subsets of data.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh