Forum Discussion
Issue Excel File Connected to Semantic Model with Analyze in Excel
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.
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.Hi Jess_54785 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you,
Tejaswi.
