Forum Discussion

divumj's avatar
divumj
Helper I
2 years ago

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...
General Comment
BA_Pete's avatar
BA_Pete
Super User
2 years ago

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

  • divumj's avatar
    divumj
    Helper I
    2 years ago

    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 

    • BA_Pete's avatar
      BA_Pete
      Super User
      2 years ago

       

      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's avatar
        divumj
        Helper I
        2 years ago

        Hi Pete 

         

        Columns are not more it was around 40, still I have identified few without which we can still go.

         

        But getting same error during group by...am I doing something wrong...

         

        Regards

        Mohit