PowerPivot - Pivot from two separate tables

Question

A txt file imported from a SAP system has got 1.5M+ rows which exceed the 1M rows inside Excel. Unable to import txt file directly into PowerQuery to perform Pivot.

I used a very primitive method to workaround this issue.

1. Manually split the txt file into 2x txt files (1M rows each).
2. Use PowerQuery to load the 2x txt files and load into 2 separate tables inside Excel.
3. Run Pivot twice to query data directly from each of the tables. Total: 2 Pivots.
4. Manually turn both Pivots into value and combine both Pivots together to come up with a master Pivot.

Please note the two tables has got the same data structure (i.e.; same columns).

Hence, looking for a more "advanced" solution. Is it possible to somehow:

1. load the entire txt file (1.5M rows) into PowerQuery?
2. if needs be - to split into 2 tables - to use a single Pivot to pivot both tables together?

(Since both tables shared the same data structure.)

Answer 1

You can import way more than 1M rows via PowerQuery in Excel. The issue you probably have is that you're loading the query into the worksheet (which does have the 1M limit).

Instead, load your query to the Data Model only...

Then you can create your pivots off that...