Reaching Excel's Limits

[ExcelAtEverything]

Hello friends,

I am working on an Excel doc in a book with more data than I've ever worked with before. I have sheets in this WB that by year's end will be clipping near the 1,048,576 max for a worksheet. I also have other sheets in the same WB which will all be doing the same. The reason is that this Excel doc that I am working on will be pulling in new data each day to be analyzed, and it's a lot of data. My sheet is already starting to hang and "think" often when I hit the enter key.

My question is... what will happen once it reaches the limit? Also, any expert advice to try to alleviate the potential issue?

Thanks!

 

[ExcelAtEverything]

Thx Michael! It's imported by me via PQuery, and before I load it into Excel I got rid of all the unnecessary & duplicate columns. I don't have it pulled up at the moment, but it's about 13 columns I think. And there are about 4 tabs of info like this. I'm thinking what I might do is just try to see if RoryA's Pivot Table idea will work. That would be the best option I think, because it would allow me to just tell the user to drop the daily incoming reports into the predesignated folder, and then all they would need to do is refresh the table. That sounds great to me. I'll likely look into Power Pivot too, but probably will avoid it for now as I don't want to bite off more than I can chew again. You and Offthelip also have very workable solutions that I need to think about also. So tonight will be me doing that.

 

[Michael M]

to just tell the user to drop the daily incoming reports into the predesignated folder, and then all they would need to do is refresh the table.

And then it could all be done by macros...
I'd still look at combining data rows AND deleting anything that could be classed as duplicates.
Maybe if you posted a small sample set of data we may be able to offer further opinions.

 

[ExcelAtEverything]

The data does not have to ever go near a worksheet. You can create a connection only query and use that as the data source for pivot tables. You can also load it to the data model and use Power Pivot, which may be a better option if you are using time intelligence functions.

So it seems to me that the best way would likely be this method.

I considered just creating my own table on a tab to house data summaries without all the added detail. This would mean that stores only needed to send the daily report with the numbers from yesterday and I would create a macro to extract only the info I need, plug it into my own data table for that date and that store on the separate tab, then discard the daily report after. But as it stands this company's reporting is far from ideal. Each store's report(s) must be run individually, as there is no report that lists the info per store all on one sheet. For this reason, 3 reports must be run each day, for each store (currently 4 with more on the way in months, & even more next year). However as I thought that through, it just feels far less complicated to just throw all those reports in their respective folders and click refresh. Besides having worked in retail leadership for so long, I know that those minutes really matter on the morning.

The only problem I'm having is one that has been plaguing me since last week. I posted the issue last week when I ran into it when trying to use PQ, & it was moved to Power BI section & I got reprimanded for cross-posting to another site that I was trying out (first time... & never again) but still no response to the question yet. I'm sure I'll be reprimanded again for this since I already asked the question, but it was either this or I reference the other post, but that to me seems like I'm putting the work on you to go read a different post of mine. But this is me now trying to do it in for a Pivot Table, so same issue but different instance of it. So not sure how to navigate that, but basically the problem is that when I try to connect to the xlsm files, I get this error (below). The file is fine, I can open it, it's not corrupted. In fact, if I open it & resave as an xlsx file and try again... Excel accesses it flawlessly. So without being able to get past this, I'll need to tell the user that they need to do the same by opening and resaving to the folder as xlsx, which seems ridiculous as I know it can be done. I can't seem to find any info on Google or any YT vids about this issue at all. Has any of you encountered that?
(Here is a link to the cross-post: Power Query - Error importing data)

 

[Michael M]

It's not a trust center setting is it ??

• 
  
• Now choose File > Options > in the navigation pane choose ‘Trust Center’
• Click ‘Trust Center Settings…’
• Then choose ‘Trusted Locations’ > click ‘Add new location…’
• And enter the folder path where you saved the workbook. (utilize browse button to locate it)
• Click OK > reopen the workbook.

 

[ExcelAtEverything]

It's not a trust center setting is it ??

• 
  
• Now choose File > Options > in the navigation pane choose ‘Trust Center’
• Click ‘Trust Center Settings…’
• Then choose ‘Trusted Locations’ > click ‘Add new location…’
• And enter the folder path where you saved the workbook. (utilize browse button to locate it)
• Click OK > reopen the workbook.

No sir. Same thing

 

[Michael M]

This from the Microsoft Community page

One of the reason could be that the data that you are importing to PBI is  not in excel table.
 Select the rows and columns from the sheet that you want to import >> Insert Table in excel and try importing it should be able to read the data then .

 

[ExcelAtEverything]

Lemme try that, but then why would converting it to xlsx all of the sudden resolve it?

 

[ExcelAtEverything]

Ok, so these are the huge files that were causing the massive slow-down when pulled into Excel before, so there is a LOT of data in these files. Hence why I was happy when RoryA told me that the Pivot option could be used w/o actually pulling the files into Excel.

When I open one of the files, there are maybe 15 tabs or so. I only need the first 2 tabs of info. The rest of the tabs are useless to me, and are all made up of Pivot Tables & dashboards with formulas in them. All returning info that I don't need. But now this seems to be presenting another issue. To convert just the first of the two tabs needed, it took about 2 minutes as there are 781k cells (65 columns of about 21k rows), and then it took another 2 minutes or so when I resaved it. Same thing w/ the second file (of which there will be even more).

When I deleted all of the extra tabs of tables etc, I decided to try it without converting them to tables, and it worked fine. So this raises a few questions for me.
1) Do I still need to convert to tables anyway for sake of data integrity, or should I just leave it if it works?
2) Is there a way to maybe setup a macro to look inside each folder inside of my main project data folder, keep the two sheets names "Data" & "Line Items" while deleting the rest, and then (if I must do it) convert the ranges on the remaining 2 tabs to tables?

Or is there something else you suggest altogether?

 

[RoryA]

If you open the source file and simply save it in its original xlsm format, can PQ then access it?

 

[ExcelAtEverything]

If you open the source file and simply save it in its original xlsm format, can PQ then access it?

RoryA, you are the man!!!!! That totally worked! I opened it, then I had to click to enable editing, then save it (still as an xlsm file). Once I did... BAM! It opened right up in PQ. That was driving me crazy! I would have never thought to even try that. Listen, is it ok for you to go to my original post about this and post this answer, then I can give you the checkmark on the relevant thread? Power Query - Error importing data