Forum Discussion
How to open an Excel with more than 30 millions lines ?
Julxoo Yes, Power Query will be the tool to use. On a Mac, though, that will be a challenge if you have never used it before. Although, Excel for the Mac (Beta) has full functionality when it comes to connecting to a CSV file, the look and feel is different from what you find in on-line tutorials based on the Windows version.
When you say, "but that didn't change anything", do you mean that you don't see the Power Query button in Data ribbon? If not, you may have to wait until your beta version gets it. New features aren't rolled out to Insiders/Beta users all at once.
When I say "that didn't change anything", this is the following step I do :
I click on "Get Data(Power Query)" > Text/CSV > Browse.
Then I reach m file, I click on Get Data > Next > Load.
When they ask me "data type selection", I click on "all data" and finally Load.
That take 4 hours to load, but then, they only show me 1'050'000 lines and not all! this is where im block...
Julxoo But, when you have so many rows you should NOT load the data into Excel as you will still have the 1 million row limit. Just connect to CSV. Do the necessary clean-up, transformations, groupings etc. and then create sub-sets of relevant data to be analyzed that do not exceed the 1 million row limit.
Having said that, you would actually need to use the Data Model (a.k.a. Power Pivot/DAX) or Power BI to deal with such enormous data sets. All of these are, unfortunately, NOT supported on the Mac (yet). Get your hands on a powerful PC or install a virtual Windows machine with Excel/Power BI on your Mac (Bootcamp or Parallels). Can't guarantee peak performance though.
This is the last things I can see before the 4 hours load. Can you juste help me about what should I do when im here to create the sub-sets and do the cleaning etc ?? Or maybe do you have good sources for me to learn that ? Video, etc... ?
Thank you so much
Julxoo Here is where I took my first steps into PQ, running Windows and Excel on my Mac via Parallels.
https://exceloffthegrid.com/power-query-introduction/
Chapter 5 deals with Close & Load options that you have on a PC. The Data Model is not available on the Mac.
And, now that I think of it, PQ on my Mac (Insider Bata) still defaults to loading every query back to Excel. It doesn't allow me to choose "Connection only", although I read that it has been introduced.
It's exciting to see that PQ has finally come to the Mac. But, as said, it's probably not yet a suitable platform to handle such massive data sets. Unless I've missed a recent development, of course.