Forum Discussion
How to exclude large historical table from refresh in Power BI?
Hi everyone!
I wanted to share how I finally resolved the issue I posted about earlier regarding excluding a large historical table from refresh in Power BI.🧩 The problem recap:
We had a large fact table (~200M rows, 93 columns), and incremental refresh failed during full dataset refresh due to timeout and memory limits – partitions weren’t being created.
We attempted a Hot & Cold Data approach by splitting into historical and current tables in Power Query and appending them, but even with “Include in refresh” disabled for historical, Power BI still queried it during refresh.
Using DAX UNION to combine both tables increased dataset size and hurt performance due to additional in-memory tables and complex measures.
✅ What worked:
We switched to custom partitioning using Tabular Editor and SQL Server Management Studio (SSMS).
I created one partition per year manually and gradually loaded data year by year.
This approach allowed us to:
Successfully configure incremental refresh.
Avoid timeouts.
Reduce memory pressure during refresh.
🚀 Bonus:
Once all partitions were added and processed, incremental refresh ran smoothly.
This method avoided unnecessary reloading of historical data during refresh and ensured performance remained stable.
I hope this helps someone facing a similar challenge. Feel free to ask if you’d like more technical details! 😊
Hi BA_Pete ,
Thanks for your response!
We actually used Incremental Refresh, and it worked well initially. However, the problem arose when we had to make changes to the dataset – each time we modified it, we had to refresh the entire dataset from scratch. Since the dataset size and number of records increased significantly, Incremental Refresh started failing consistently, throwing timeout errors. Eventually, the capacity got overloaded and stopped working altogether.
We also tried managing partitions via XMLA, but that required loading both the historical and current tables into the model and then combining them using DAX UNION, which didn’t seem optimal in terms of performance.
A few questions regarding your recommendations:
1) Do you have any recommendations for handling Incremental Refresh in cases where dataset size is too large, and full refresh becomes unmanageable?
2) Regarding custom partitions – are there any hidden pitfalls or challenges we should be aware of before implementing this approach?
Super UserHi katushka_enko ,
1) Yes, load the incremental refresh history in chunks using custom partitions.
2) Yes:
i) It's pretty technical and requires more advanced skills and knowledge. It's not the sort of thing you will easily be able to hand over to a new/junior developer and expect them to be able to pick it up straight away.
ii) Using XMLA endpoints prevents you from being able to download items (models etc.) back out of the Service. This is no different from incrementally refreshed models, but can have wider-reaching effects depending on how many touchpoints you actually use this method for.
As I said previously, custom partitions aren't for the faint of heart. In addition to just getting the partitions created that can be run manually, you'll probably want/need to set up some Power Automate structure(s) behind it to handle the loads and manage partition successes/failures etc.
You can also do all this into dataflows, set the Enhanced Compute Engine to ON, then Direct Query + Agg table into your model for reporting, or you can just break up all of your history and current data into dataflows of smaller equal parts (e.g. fiscal year or similar) and slowly combine them through a series of dataflows or within your model.
Once you start getting into these types of data sizes, your options obviously become more limited. I don't think you'll get to a place where it performs like a 1m row import model. You'll probably need to test a number of these options, or combinations of them, and pick whichever is the best of the bad bunch.
Pete