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]

right click on Taskbar, choose Task Manager then Performance

 

[sandy666]

check OS and Office for latest update

 

[sandy666]

or try to run windows in a safe mode then try to load transformed tables into Data Model

 

[sandy666]

btw. for Data Model number of rows for each table cannot exceed ca. 2000000000 ( 1999999997 )
so just in case count rows after unpivoting and merge

 

[SuperNerd]

ok- I believe that's the problem. It loads beyond 2.1B rows, and then crashes/errors. So, do I need to cut my tables in 1/2, do the merges, then load the results separately and append in Power Pivot after? Can you even do that in power pivot?

 

[sandy666]

It's up to you what you will do
It seems the data model is the source of the problem

I loaded over 25 mil rows (25402030) into the Power Query and it works well, slow but works (4.5 GB)
with Data Model it says: Limited to 2 GB

 

[sandy666]

maybe try PowerBI Desktop for tests