Working with very LARGE Datasets | 4+ Million Rows | Power Query and Power Pivot | Big Data in Excel
Scenario Discussed:
1. Getting Data from 4 Excel Workbooks saved in a Folder.
2. Each Excel Workbook contains a Worksheet which consists a data of 1,048,576 rows in it.
3. Each Excel Worksheet Name is Different & Data is stored as Table and Names are Different too.
4. Each file has a data of 1,048,576 rows which means 4 Excel Files
1,048,576 X 4 = 4,194,304 ROWS
4+ Million Rows
5. The Number of Columns in Each Excel Workbooks are not same, and Sequence is also not Same and Few Additional Columns also added in some files.
With Power Pivot for Excel, there is theoretically no limit on the number of rows of data.
The actual limitation depends on the version of Microsoft Excel you are running and whether you are going to publish your spreadsheet to SharePoint.
If you're running the 64-bit version of Excel, Power Pivot can reportedly handle about 2 GB of data, but you also must have enough RAM to make this work smoothly.
Note: You can use Power Pivot in Excel 2019, 2016, 2013, 2010, and Excel for Microsoft 365.
