I have a table that has 140,000 rows with data and that is only 6 month's worth so it could potentially go up to 300,000 or more records by the end of the year.
Originally I set up my workbook with a Dashboard Configuration with this table inside, along with multiple Pivot Tables (7 each) populating the Dashboard Data using Slicers. The issue was the file size was at 42MB and that is with it formatted as binary xlsb.
So, I re-grouped and came up with an idea to remove this table and store the it in another workbook. Then I would use Power Query to link the Pivot Tables. At first I was excited because the workbook size fell from 42MB to just 2MB and the file was fast. For the Pivot Tables I unchecked "Save Source data with File" in Pivot Table Options. I also unchecked Refresh Data when opening the file options. In PQ I also disabled Enable Background Refresh and Refresh data when opening the file. The data in the Table will only change once a month and my plan was to refresh the data in the workbook when this occurred.
My Issue is when I had another person open the file and try it out when they selected one of the slicers it stated the data had to be refreshed first (Not everyone has access to the table stored in another workbook and I don't want them to). So, I had them refresh anyway and it took a while (they had access but it took 3-4 mins to refresh). Once the refresh completed they were able to perform slicer selections successfully.
Is there anyway to keep the file size low and make use of the data without refreshing it?
If I don't use PQ and store the data within the file may get too large and too slow and no one will use it.
If I use PQ and store the data remotely then while the file size may go down tremendously, data refresh has to occur and it takes way too long. I'm danged if I do and danged if I don't.
On a side note: I got to learn Python so I can get myself out of these Excel nightmares.
