Consolidating excel with a million of rows
- Thread starter prakash
- Start date
Chihiro
Excel Ninja
If you have Excel 2013 or later and have PowerQuery.
Query and merge all sheets in PQ editor. Load as connection only and to data model. Use resulting model as PivotTable source.
If using older version or do not have PQ. It's going to be significantly more difficult to do this and will require some knowledge of VBA and/or databases.
1. Using vba load each sheet into array and merge them in memory. Do note that this requires relatively powerful machine with large memory (RAM). Then load the array into PivotCache directly. You can find sample method in link below.
https://chandoo.org/forum/threads/combine-two-table-via-vba-create-pivot-from-array.39351/
2. Load data to external db, consolidate to single table. And use that as source for your PivotTable data.
3. Use intermediate workbook/sheets to aggregate data before loading to PivotTable. Note that you will lose ability to drill down to raw data level with this approach. If you need that level of drill down... you'll need to create system using VBA to extract source data from aggregated data.
Query and merge all sheets in PQ editor. Load as connection only and to data model. Use resulting model as PivotTable source.
If using older version or do not have PQ. It's going to be significantly more difficult to do this and will require some knowledge of VBA and/or databases.
1. Using vba load each sheet into array and merge them in memory. Do note that this requires relatively powerful machine with large memory (RAM). Then load the array into PivotCache directly. You can find sample method in link below.
https://chandoo.org/forum/threads/combine-two-table-via-vba-create-pivot-from-array.39351/
2. Load data to external db, consolidate to single table. And use that as source for your PivotTable data.
3. Use intermediate workbook/sheets to aggregate data before loading to PivotTable. Note that you will lose ability to drill down to raw data level with this approach. If you need that level of drill down... you'll need to create system using VBA to extract source data from aggregated data.
GraH - Guido
Well-Known Member
Hi There,
I'm using excel 2016 version. All 3 sheets are in the same workbook.
regards
Prakash S
Chihiro
Excel Ninja
Then just use PQ/Get & Transform.
Follow tutorial in the link.
https://blog.crossjoin.co.uk/2018/07/09/power-bi-combine-multiple-excel-worksheets/
It uses PowerBI, but most of the process is the same. Only difference being, after you append all sheets into one query/table. Instead of loading to sheets, use "Close & Load To..." and specify connection only and load to data model.
Follow tutorial in the link.
https://blog.crossjoin.co.uk/2018/07/09/power-bi-combine-multiple-excel-worksheets/
It uses PowerBI, but most of the process is the same. Only difference being, after you append all sheets into one query/table. Instead of loading to sheets, use "Close & Load To..." and specify connection only and load to data model.