Forum Discussion
Merging two big files
Good day all
I am trying to pick one column from another table basis certain conditions met and getting error message
Expression: Error: Evaluation ran out of memory and can't continue .
My both table size is huge...one is having 2.9 mn rows and second is having 3.3 mn rows.
My end result is output in pivot table. Any suggestions how to handle such big data in power query or is it not possible at all to work on such huge data.
8 Replies
Hi divumj ,
I'm assuming you're doing this in Excel as you've stated the output is a pivot table.
Based on this assumption, you have the following memory limits (also assuming you're using Excel 2013 or later):
- 32bit Excel in 32bit environment (operating system) = 3GB
- 32bit Excel in 64bit OS = 4GB
- 64bit Excel in 64bit OS = Max system GB
Merging in Power Query is a 'whole-table' operation, i.e. the whole table needs to be loaded into memory (or at least every row scanned) to perform. This is likely to cause you issues with tables of this size in the first two scenarios above. Add to that the fact it sounds like you're doing a conditional merge ("basis certain conditions met"), and you're going to start hitting problems even with a 64/64 setup with limited system RAM (4GB/6GB, maybe even 8GB+).
Without knowing your Excel/OS setup I can't really say whether adding physical RAM to your machine could/would help. However, stripping your tables down to the bare minimum number of rows and columns before doing the merge will always help, so I'd certainly start there.
Pete
Hi Pete
Thanks for the response I am doing it on Excel 365 and in official environment so space shouldn't be the issue...but still I can check if I can reduce the size of columns..
My Ram size is 8GB and OS is 64 bit
Regards
Mohit Jain
It's not space that's the problem, it's addressable memory (RAM).
Have a go with stripping back the table columns/rows before the merge and see how you get on.
Let me know if you're still struggling as there may be other options available utilising the Excel Data Model.
Pete
divumj - so BA_Pete is right that memory limitation Power Query will hold you back, but if you can eloborate on the requirement "pick one column from another table based on certain conditions" by showing a sample of the data and your M code. It is possible that the Table.Group will help, but you may need to add Table.Buffer step to stop Power Query from re-generating the Table.Group step.
Hi Anonymous
Thanks for suggestions, I am doing group by from transformation tab which I think is similar to Table.group correct me if I am wrong.
Table 1 - previous month data
Column A Column B Column C
Table 2 - current month data
Column A Column B Column C
I am trying, in case column B is blank than pull value from previous month if columns C data matches.
For this I have tried merging option also I tried creating M query.
Regards
Mohit Jain
