load excel data set

[JS10]

Looking for recommended settings in Power BI to load large excel data sets.  

I have attempted to change within Power BI File<Options<Data Load<Parallel loading of tables.    Next going to try to edit the registry based on the article found to Optimize Power BI.  However, when loading the data set the memory is still spiking to a near 100 percent even with changing the setting within power bi.

I have also changed the excel file to be a excel binary workbook.

Are there recommended settings for loading large excel data sets in Power BI. also changed the excel file to be a excel binary workbook.

[Anonymous]

Hi @JS10 

When working with large Excel datasets in Power BI, it's important to optimize the data load process to manage memory usage effectively and prevent spikes. Here are some strategies you can implement:

Incremental Refresh: Instead of loading the entire dataset at once, use the Incremental Refresh feature to load only the data that has changed. This can significantly reduce memory usage during refreshes.

Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn

Optimize Data Model: Review your data model for any unnecessary columns or tables that can be removed. Also, consider aggregating data at a higher level if detailed granularity is not required for analysis.

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

Manage Parallel Loading: While you've already adjusted the parallel loading of tables, ensure that the number of tables being loaded in parallel does not exceed the capacity of your system. You might need to experiment with this setting to find the optimal balance for your specific environment.

Regarding memory spikes, here are a few additional considerations:

Upgrade Power BI Capacity: If you're using Power BI Pro, consider moving to Power BI Premium or Premium Per User (PPU) for higher capacity and better memory management.

Large semantic models in Power BI Premium - Power BI | Microsoft Learn

Use Binary Format: Converting your Excel files to binary format (.xlsb) is a good step as it can reduce file size and improve load times.

Optimize Excel Source: Ensure that your Excel workbook is as clean as possible - remove any unnecessary formatting, pivot tables, or charts that are not needed for the Power BI analysis.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JS10]

Question are you using Power BI Pro and did the benefits really help with the loading of the large data sets?

I have been working through the optimization tool mange the CPU and memory capacity for our environment.   Our computer lab has 8 GIG of memory.  I have changed the environment setting to 2 simultaneous evaluations and 64 for the max memory.  Has helped to load the excel spreadsheets.

[audreygerred]

Hi! Just a heads up. Excel binary files will not refresh in Power BI service (however, it might with a gateway). Ideally, if you or someone could load the Excel files to a data warehouse, that would be your best bet.