Queries related to data size in Power Query Editor

[coolsandeee]

Hello everyone, 

I have a few questions related to Power Query editor, esp., about the data size. 

Question #1: 

What is the maximum size of data that a Power Query Editor can load? Does it depend on the RAM of the machine where Power BI Desktop or the On-Premises data gateway is installed? If not, kindly share the relevant documentation about the Power Query Editor's limitations.

Question #2: 

I am guessing that the maximum size of the data that can get loaded into Power Pivot depends on the RAM of the machine where Power BI Desktop or the On-Premises data gateway is installed. Is my guess correct?

Any insights would be of great help.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @jennratten, @karansingla0311, for your response.

Hi @coolsandeee,

We appreciate your inquiry on the Microsoft Fabric Community Forum.

Based on my understanding, Power Query Editor does not have a fixed row limit. The limit depends on the available system memory (RAM) and whether you are using a 64-bit or 32-bit version. For Power BI Desktop and On-Premises Data Gateway, the data size you can load and process depends on the RAM and available virtual memory on your machine.

The error message, "Evaluation ran out of memory and can't continue," indicates that the transformation—particularly the unpivot operation—exceeded the available memory during refresh. This is more likely to occur with large datasets (for example, 1 million rows) and when multiple refreshes run in parallel on a gateway machine with 16 GB RAM.

Kindly follow the steps below, which may help resolve the issue:

1. Optimize query steps by moving memory-intensive operations such as unpivoting upstream (at the source system or Power BI Dataflows). Reduce the dataset size before applying transformations by filtering or removing unnecessary columns.
2. Avoid running multiple heavy refreshes simultaneously on the same gateway.
3. Use Performance Monitor to check memory and CPU usage during refresh.
4. Consider increasing the Gateway RAM to 32 GB or more if you frequently run concurrent heavy refreshes.

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar issues.

Thank you.

[karansingla0311]

Question #1: 

What is the maximum size of data that a Power Query Editor can load? Does it depend on the RAM of the machine where Power BI Desktop or the On-Premises data gateway is installed? If not, kindly share the relevant documentation about the Power Query Editor's limitations.

Answer: For PowerBi Desktop maximum data size (Power Query) depends on the RAM of the local machine running your Power BI Desktop. RAM of the machine running on the On-Premises Data Gateway comes into picture when you do scheduled refreshes because the gateway temporarily loads data into memory.

Question #2: 

I am guessing that the maximum size of the data that can get loaded into Power Pivot depends on the RAM of the machine where Power BI Desktop or the On-Premises data gateway is installed. Is my guess correct?

Answer: Yes, I believe your understanding is correct.. the maximum data size depends on the RAM of machine running PowerBI Desktop or datagateway

Let me know if this helps 🙂

[coolsandeee]

@jennratten 

Thank you for the info and the links. 

Also, this was the error message that was recieved when refresh was done through on-premises data gateway.

Evaluation ran out of memory and can't continue.

To give more insights, on that day there were a million records that were to be processed and there is an unpivot operation as well. 

On other days, it was approx. 5K records.

Do you have any suggestions here?

[jennratten]

There are a lot of factors here as well - for example, is your gateway on a machine dedicated to gateway jobs?  Were any other refreshes running at the same time?  Generally speaking, a million records should not use all the memory.  Unpivots are a memory intensive operation, but if you are also seeing this error with only 5k records, I would think there may be a combination of lack of resources and query optimizations needed. Can you tell me more?

[coolsandeee]

There is a dedicated machine for these refreshes. It has a 4 core, 16 GB RAM. 

There were 2-3 other report refreshes running along with this failed one. 

The error occurred only for a million records and not for the 5K one. 

[jennratten]

Hello @coolsandeee -

Power Query limitations can vary based on different factors like the amount of virtual memory available, the software version being utilized, etc. For example, the 64 bit version's limitation usually corresponds to the amount of virtual memory available while the 32 bit version is somewhere around 1 gb depending on the data stream. There are also things to consider like the persistent cache and query editor preview.  Here is a link to more documentation on Power Query limitations.

https://support.microsoft.com/en-us/office/power-query-specifications-and-limits-in-excel-5fb2807c-1... 

The model does have separate limitations which do relate to the RAM of the desktop (if running PowerBI desktop) or the gateway (if refreshes are running through a gateway).  The software version is also one of the factors.  Here is a link to the documentation for the model limitations.

https://support.microsoft.com/en-us/office/data-model-specification-and-limits-19aa79f8-e6e8-45a8-9b... 

Please let me know if you have any other questions.