The Billion Rows Excel Challenge

Will it pass the ultimate stress test?

Back in December 2021, How Fast is Fast Data Load in Power Query? demonstrated how long it took to load varying-sized datasets into the Data Model with and without Power Query’s Fast Data Load enabled.

What’s changed since then? Well, I got a new MacBook Pro, although I confess it’s been two years since I bought it, so it’s well past the honeymoon period now.

No, I don’t bother with Excel for Mac. I have Windows installed as a Parallels Desktop virtual machine – just so I can use the proper Excel. If you’re on macOS, I suggest you do the same.

Nevertheless, one regret from last time is I didn’t go as far as I could. Sure, I went up to 100 million rows of data, but there was potential to go much higher. After all, the Data Model’s theoretical limit is 1,999,999,997 rows.

Trust me, I did try.

Just before going to bed, I would start the process optimistic that I’d wake up hours later with a successful import.

However, there were a few issues:

• The files became so big I ran out of hard drive space.
• I repeatedly failed to allocate enough memory to the virtual machine.
• My previous MacBook Pro was prone…