2Bill+ Row Merge - Memory Error

[SuperNerd]

I have 2 tables I have unpivoted so I can merge them together. 1 has 1mil+ rows, the other has 4mil+. I am trying to merge the queries so I can then take it into Power Pivot and run more complex operations. However, I'm getting Memory errors when I try to load it into the Data Model (which, I guess you have to do in order to use Power Pivot?) I can actually get the queries to merge in Power Query, but when I close/load to the data model- it doesn't work.

I'm using 64-Bit Excel 365 on an i7 w/ 16GB ram, SSD and saving on a NAS over the network.

Here's the query:
let
Source = EntryCombos,
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"COMBONUMBER"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Value"}, IndAllUnPivoted, {"Value"}, "IndAllUnPivoted", JoinKind.LeftOuter),
#"Removed Columns1" = Table.RemoveColumns(#"Merged Queries",{"Value"}),
#"Expanded IndAllUnPivoted" = Table.ExpandTableColumn(#"Removed Columns1", "IndAllUnPivoted", {"INDEX"}, {"INDEX"})
in
#"Expanded IndAllUnPivoted"

Here's the error (it won't let me copy/paste, so I got a screenshot of it...

I have tried MANY different approaches (rebooting, restarting excel, loading the source files into the data model, NOT loading them into the model, etc- nothing works). Any ideas of what I can do?

Thank you!

 

[sandy666]

where is the screenshot?

 

[SuperNerd]

Weird. It showed it uploaded. I’ll upload when I get back in. It basically says it’s a memory error repeated hundred of times.

 

[SuperNerd]

Here you go- what do you think?

 

[sandy666]

You've an answer on your picture
- importing data to Power Pivot or a Tabular model uses more RAM than the source data size (almost twice)
- check memory utilization while you trying refresh table

Large Address Aware capability change for Excel - Microsoft 365 Apps

Describes the added Large Address Aware functionality for Excel 2013, which lets Windows allocate increased RAM to the Excel. This capability was added by the May and June updates in 2016.

docs.microsoft.com

 

[SuperNerd]

I guess I don't understand how to get this to work? I have the 64-bit version already, 16GB of ram and no other applications running. Any suggestions on how to get this to work?
Thank you

 

[sandy666]

you can try increase pagefile size and swapfile size
but, as I said, check memory utilization while you trying to load data into Data Model
I can't say more until to know what are you doing there step by step until you see the error message

 

[sandy666]

btw. how big (size) is your source data?

 

[SuperNerd]

Source 1- 68MB, 950k+ rows, 6 cols (next step is to unpivot and this becomes 2 cols w/ over 4mil rows)
Source 2- 168MB, 4mil+ rows, 2 cols

How do I check memory utilization? Just Ctrl+Alt+Del and see while it's loading to the Data Model?

 

[SuperNerd]

FYI- while performing the load to data model I’m showing only 1.5GB of ram being used as per the task manager.