Forum Discussion
Issue Excel File Connected to Semantic Model with Analyze in Excel
Hello,
Since August 26th, some users (including myself) have been experiencing a problem refreshing Power BI datasets in Excel. The error message is:
“We couldn’t get data from an external data source. Please try again later. The automatic existence operation exceeds the authorized memory limit and is being cancelled.”
The dataset sizes vary from light to heavy. We compared memory usage across machines and found that some users with 90% memory usage can refresh successfully, while others with only 54% memory usage cannot.
I have tried resetting Excel, but the issue persists. A colleague logged into their Microsoft account on my machine and was able to refresh the dataset without errors.
Any insights on how to resolve this would be greatly appreciated.
Best regards, Jess
Hi Jess_54785 ,
Thanks for sharing the additional details. Since you and your colleague have the same Pro licence and roles, and you’ve already tried reducing the query size, updating Excel and disabling “Show items with no data”, this rules out role security or configuration issues. The error is actually coming from the way “Analyze in Excel” runs queries on shared Pro capacity. In Pro, the per-query memory limit is fixed and once the auto-exist calculation crosses that threshold, the engine cancels the query. That’s why it works with lighter pivots but fails when the query is heavy.
On Pro capacity, there isn’t an option to increase this memory limit. The only workarounds are to keep the queries as lean as possible in Excel (apply filters at the top level, avoid using too many high-cardinality fields together, and rely on measures instead of multiple raw fields). If you often need to pull larger data into Excel, moving the dataset to a Premium or PPU workspace will help, since those have higher memory allowances.
Thank you,Tejaswi.
9 Replies
Hi Jess_54785
This error comes from the model’s engine, not Excel. The pivot query sent by Analyze in Excel triggers a very large auto-exist operation, and the capacity cancels it because it exceeds the per-query memory limit. That’s why it started recently and why it may work for another user/time but fail for you.
Could you please try below steps:
-
Reduce the query size: apply a required top-level filter (e.g., Date/Region) before Refresh; avoid putting several high-cardinality columns on Rows/Filters; turn off Show items with no data.
-
If you must return everything, run the model on Premium/PPU (or ask the capacity admin to raise the Query memory limit), then try again.
-
Make sure you’re on 64-bit Excel and the latest Analyze in Excel/MSOLAP add-in (won’t fix the server limit, but avoids client-side issues).
Hi rohit1991 , thank you for your comment.
I have tried reduce the size of the query by applying a filter & updates Excel/PBI on 64-bit & removing the shoff items with no data, but the error message stayed. For the capacity, we don't have one; we are just using a workspace with PBI Pro.
My collegue and I opened the same Excel file, with same amount of data, they were still able to refresh it but I wasn't. We both tried at the same time and at different time; maybe it would mean the per-query memory limit was reached for my account but not my colleague.Hi Jess_54785 ,
Thank you for providing such a thorough follow-up. Since you’ve already cleared your cache, updated Excel, and confirmed that the dataset works for your colleague even on your own machine it’s clear this is an account-specific issue within Power BI, not Excel or your device.
Analyze in Excel processes queries based on each user’s security context. Even as an admin, your account could be subject to a specific security role or an effective permission at the dataset level, which may lead to larger queries and trigger the “auto-exist operation exceeds memory” error.
I recommend reviewing the Manage permissions settings for the dataset to see if your account has any extra roles or filters compared to your colleague. You can also test with a simple pivot (like just Year or Region) to confirm if your user context is causing the heavier queries.
If your workspace is on Premium or PPU, check with your capacity admin to ensure your account is fully licensed and see if the per-query memory limit can be increased. Sometimes, removing and re-adding your license or permissions resolves these account-level issues.
Given that the same dataset and machine work for others, it’s almost certain the issue is tied to how your account is evaluated in the model engine. Here’s a solved thread that addresses a similar problem and offers additional workarounds:
Auto exist operation is exceeding permitted limit, Excel – Solved Thread
Thank you,
Tejaswi.
-
Issue cause: It’s account-specific (since colleague login works on your PC). Likely due to license/role differences or corrupted Excel/Power BI cache.
Fix steps:
Clear Excel/Power BI cache (%AppData%\Local\Temp\OLAP and AnalysisServicesWorkspaces).
Update Excel + latest MSOLAP OLE DB driver.
Check Power BI roles/security – RLS or different permissions may trigger heavier queries.
If on Premium, check capacity memory limits.
Try reassigning the Power BI license to the failing user.
Hi, thank you for your answer. I have tried to clear the Excel&PBI cache & update to the last version , but I still unfortunately get the same error. My colleague and I are both admin, in the workspace.
The refresh worked for the admin role and another colleague with RLS applied.
Hi Jess_54785 ,
Thanks for raising this. The error message is actually coming from the Power BI engine and not from Excel itself. What happens here is that the query generated through Analyse in Excel sometimes triggers a very large “auto-exist” calculation, and the capacity cancels it once it crosses the authorised per-query memory limit. That explains why it started recently and why it may work for one user but fail for another.
Since you also noticed that your colleague could refresh the dataset on the same machine with their login, it points to account-specific differences as well. This could be due to role security (RLS), permissions or even licensing, which can make the query heavier for one user.
A few things you can try: clear the Excel/Power BI cache folders ensure you are on 64-bit Excel with the latest MSOLAP driver, and check if the account has identical roles/licence as your colleague. On the Excel side, try reducing the query size by applying top-level filters (like Date/Region), avoiding high-cardinality columns on rows, and turning off “Show items with no data”. If the requirement is to pull the full dataset, please check with your capacity admin if the query memory limit can be increased or if the dataset can be run on Premium/PPU.
Hope this helps. Please let us know if you still face issues after these steps.
Best regards,
Tejaswi.Thank you for your answer, unfornately it doesn't seem to be the licensing, nor the role security has we has the same license PRO) & roles.
I have tried reduce the size of the query by applying a filter & updates Excel/PBI on 64-bit & removing the shoff items with no data, but the error message stayed. For the capacity, we don't have one; we are just using a workspace with PBI Pro.Hi Jess_54785 ,
Thanks for sharing the additional details. Since you and your colleague have the same Pro licence and roles, and you’ve already tried reducing the query size, updating Excel and disabling “Show items with no data”, this rules out role security or configuration issues. The error is actually coming from the way “Analyze in Excel” runs queries on shared Pro capacity. In Pro, the per-query memory limit is fixed and once the auto-exist calculation crosses that threshold, the engine cancels the query. That’s why it works with lighter pivots but fails when the query is heavy.
On Pro capacity, there isn’t an option to increase this memory limit. The only workarounds are to keep the queries as lean as possible in Excel (apply filters at the top level, avoid using too many high-cardinality fields together, and rely on measures instead of multiple raw fields). If you often need to pull larger data into Excel, moving the dataset to a Premium or PPU workspace will help, since those have higher memory allowances.
Thank you,Tejaswi.
