Working with very LARGE Datasets | 4+ Million Rows | Power Query and Power Pivot | Big Data in Excel

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.

[akshatabhat]

Thanks for the video. I have a question, does it work if a sheet has multiple tabs with more than 1 million rows or should I split data in between different files with one tab

[johnmontojo]

Thank for your video, I have tried it and it work, and I am now refer to this video every time I need to consolidate

[TheExtravaganza]

Nice explanation ! Thank you

I have a question that I did the similar thing to import files into folders and then did some merging and my final table was unable to load all rows on sheet. Then I got a solution to add them in a data model which worked perfectly. But problem occurs when i added more files like it went upto 31 Million rows.

Why it takes empty rows in count and does it impact in the file like more than 30 Millions rows. and I need to add like 100 of sheets more in the folder in coming days. It takes all my disk space and then gives me error. how can I fix this problem permenantly like it takes the rows only which have data and show me less rows are loaded to avoid any extra data usage.

If you didn't understand my concern. I will explain it again, please let me know.

[peterchristopherson]

At 11:35, you select "Only Create Connection". I'm not able to select that option, is there something that I need to do for this to be possible for me? Thank you in advance and thank you very much for uploading this content!

[MrJaschan]

If we keep on adding the raw data in the power query data source folder every month but want to keep data only the fresh one not the duplicate as compared to previous months, how to resolve this

[sandeepbondre]

What if there is only one table that gets populated or appended with 4million rows will Power Pivots & query still work

[Mega_world]

While I am connecting sharepoint excel file to power bi, unable to transform as it is giving error as the data in the preview has been truncated due to size limits. What can i do to link pls help

[chetnajha.]

Why I am not getting table option under kind coloumn.. please reply I am stuck there

[luckee]

How to convery this data model in CSV or this combined data into CSV

[xrlyyki]

Why the M language code is not working for me ? it just don't give any suggestions when I want to create custom column.

[hasinarafenoarisoa]

how to go to the powerpivot if data in powerquery is in only connexion, please!

[xhielajade]

any idea how to fix this error, it says 1, 357, 599 rows loaded 815, 889 error. I am trying to add connection to be use in Data Model. Thanks