How to optimizing data processing for Advanced Excel and Power BI Users: Handling Large Monthly Data

[nor303]

I have multiple Excel sheets/workbooks containing monthly data, which include a lot of unnecessary information that I don't need, along with some essential data. Customers are categorized into two main categories A or B, with several subcategories under each. My goal is to summarize the subcategories and create a time series to track month-to-month changes, presenting this data in Power BI. Ideally, I want to place the data in a folder and have it update automatically. I've created a query with three files, resulting in approximately 160,000 rows and 35 columns. Excel crashes when I try to update this query, and I wonder if there's a way to reduce the data to make it easier to work with. I'm simply looking for the most efficient and effective way to accomplish this.

[BA_Pete · Accepted solution]

Hi @nor303 ,

To update dynamically from a folder: Put all similar files into the same folder (either SharePoint or Network location - SP won't need a gateway to refresh, network will) and use the folder connector in Power Query to import, transform, and append them all in one go.

To avoid Excel crashes: Difficult one this without being able to see your exact scenario, but 160k rows/35 columns is not a particularly large dataset, so it may be one of the following things that's causing your crashing:

-1- If using 32-bit version of Excel you'll be limited to about 1GB of virtual memory use.

-2- Using lots of whole-table operations (sort/merge/pivot etc.) will fill up virtual memory and can cause poor performance and/or crashing. If you're doing this along with point -1-, you'll really struggle.

-3- The only way to reduce data imported by PQ when using Excel files as a source is to remove data within each source file. If your data quantity starts to grow excessively, you might consider pushing the Excel files into an SQL DB, in which case you can fold your PQ queries to reduce the quantity of data imported.

Pete