I have been working on an interactive Excel dashboard that utilizes pivot tables and slicers. The dashboard integrates data from three different sources, resulting in a total of at least 900,000 rows of data. Consequently, the final Excel file size amounts to at least 100MB.
My current approach involves loading all three data sources into separate sheets within Excel. Whenever new data becomes available, I simply add it to these sheets. Subsequently, I employ Power Query to cleanse and filter the data, removing any unnecessary headers or zero-value entries. This process generates three clean tables, which I can then utilize to create pivot tables and construct the dashboard. Importantly, the three Power Query tables are updated automatically if any new data is added to the source sheets.
However, I have encountered some performance issues with this method. The initial loading time is lengthy, and even when I attempt to manipulate the slicers to modify dates, products, and other parameters, the process remains sluggish.
I am seeking advice on whether there is a more efficient approach to address these challenges. Any suggestions would be greatly appreciated.
Thank you.
Bookmarks