Large Excel files in Powerpivot - best practice?

[Bilingual]

Hi, i have 3 xlsx. files in exactly same format each numbering about 750.000 rows, i can export either as xlsx, csv or txt.
I have tried in vain to create a single Excel file by copying data in one file, but Excel crashes every time.

So here is my questions:

1. Can i consolidate the files in any way in Powerpivot? - i have tried using Consolidate in Power Query but im not sure how to use it.
2. Any alternatives? - consolidate the data in Access and then use it in Powerpivot?
3. Will exporting the data in .txt or .csv make it less memory heavy?

I only have access to Office 2013 32 bit on my work, which I unfortunately can not change to 64 bit.

Kind regards Daniel

 

[sandy666]

you can try Alt+D+P (pivot)
or
Append in PQ

 

[Fazza]

Hello, Daniel

I'm unclear what you want to do.

If it helps, any version of Excel in the last 20 years can make a pivot table from the three files - so you can have three data files & a pivot table in its own file (or in one of the three data files). Like Sandy already answered, ALT-D-P to get started. Then choose external data source & follow the wizard. For the three files, the SQL can be like below.

HTH

SELECT *
FROM first
UNION ALL
SELECT *
FROM second
UNION ALL
SELECT *
FROM third

 

[Bilingual]

Hi Fazza
I need Powerpivot to relate to other Tabels and to create a Pivot from there.
The Excel files are exported from SAP and are each a dataset for one year.
Its not usually a problem, but because of the huge size of the Excel files i can not in Excel combine to one sheet.

 

[sandy666]

So use PQ with DataModel for these three excel files then relate it with other tables in PowerPivot and create PivotTable as result to the sheet

 

[Fazza]

Hi Fazza
I need Powerpivot to relate to other Tabels and to create a Pivot from there.
The Excel files are exported from SAP and are each a dataset for one year.
Its not usually a problem, but because of the huge size of the Excel files i can not in Excel combine to one sheet.

I understand you want Powerpivot.

However I'm not sure you understand that the task can be done with normal pivot tables - any pivot table can join data from three source files. There is no need to combine the source data to one sheet.

Anyway, have you now got a solution with power query?