Merging two big files

[divumj]

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.

[divumj]

Hi @Daryl-Lynch-Bzy @BA_Pete 

Thanks for guiding on this...it did worked partially....so as of we have switched the tool... 

Regards

Mohit Jain

[Daryl-Lynch-Bzy]

@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.

[divumj]

Hi @Daryl-Lynch-Bzy 

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

[BA_Pete]

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]

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]

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]

Hi Pete 

Getting same error message when I tried to group by for just 6 columns....

It seems some problem with particular column.

Regards

Mohit

[divumj]

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