load excel data set

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.