Forum Discussion
Analyze in Excel Memory Error
//same issue also exists on stackoverflow (https://stackoverflow.com/questions/63155367/how-to-solve-excel-memory-error-a-record-that-exceeds-the-max-page-size-of-the)
It would be great if there where a possibility to have the size limit increased or lifted!
We encountered the same issue with a PowerBI dataset which is continuously growing as we include more tables (and therefore columns) to the model. The error seems to be specific to excel and not limited to PowerBI. Apparently the size limitation is on a row / record level where a single record exceeds in the case of excel 65528 bytes. Most likely there might be a similar limitation in other tools: https://www.ibm.com/support/pages/record-size-exceeds-internal-limit-65535-bytes
As seen in the IBM help article a potential solution might be to check if there are any columns with particularly long text fields.
I already tried to narrow down the columns which might cause the issue by extracting the table sizes using DAX studio, however, did not find a particular culprit as the size of the columns caused by the number of lines/records does not relate to the size limitation on one record causing the issue in excel. As reference, the largest column in our dataset is the date field in the date table, which according to DAX studio is 10028.046875 KB large, but removing it only decreases the size of the record issue in excel by 40 byte from 92929 byte to 92889 byte.
In case you want to check the same on your dataset (link was already posted by vanessafvg in this tread):
https://exceleratorbi.com.au/table-size-from-power-bi-desktop/
Hence in our case large record size is likely not caused by one particular column but the extensive amount of columns.
As found out with trial and error, the size limitation is affected by all tables available in the dataset even if some might be not connected via relationships.
Interim solution will be to use a subset/perspective of the dataset for excel analysis purposes.
Overall steps to narrow down a solution in your case:
- check for large columns and potentially remove them
- keep your dataset as lean as possible, remove unnecessary / unneeded columns
- work with part of the dataset for excel analysis purposes which only includes the data needed in excel
-> all of these steps will help you to decrease your overall record size until you are below the size limit
Appreciate if someone found and could share a better solution to this issue which does not require the dataset to be reduced in size. This is the best solution I am so far able to come up with.
Related posts to this issue:
Hi there, i had the same issue:
Re: Refresh error / Exceed maximum page size of th... - Microsoft Fabric Community