Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.