Solved: Re: Excel Power Query Refresh

lherbert501

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 523k rows and it's cutting off at 500k which I presume is the limit. My aim is for the user to have a refreshed spreadsheet without having to go through database permissions.

Is there an alternative approach that could work with this?

Thanks

PwerQueryKees

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.

View solution in original post

v-dineshya

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...

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

View solution in original post

PwerQueryKees

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.

lherbert501

Hi @PwerQueryKees

Is there any documentation anywhere on this method?

I'm interested to try it but not quite sure I understand it fully.

Thanks

PwerQueryKees

No documentation as such. In Excel you can get data from a SharePoint folder. And after selecting the file you are after, open it and access it's data. It al depends a bit of how your data is structured. I am sure you can find videos on accessin Excel files on SharePoint or on a network drive.

Kees Stolker

A big fan of Power Query and Excel

PwerQueryKees

For example https://youtu.be/-XE7HEZbQiY?si=2STX9o7FxoDVoANX

v-dineshya

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