"Working on it" Frustration

[IntaBruce]

Power BI is is frequently getting stuck in a loop of "Working on it", with no way out except closing the app and going through the painful process of recovering the file.  This started yesterday, and has become more frequent, each time I add a column to a table or click on Transform data to go to Power Query.

I have tried a reboot and have even uninstalled and reinstalled Power BI Desktop.

My model is quite big (in my view) with over 120 tables from two sql databases, SharePoint, Excel, and a few hand keyed.  There are several hundred thousand rows in some tables.

Have I overstretched Power BI and need to do some pruning (or tuning)?  I'm no data engineer, just an overenthusiastic citizen developer so I'm bound to be doing something wrong.

Any help or suggestions appreciated.

[Alex_Sawdo]

Generally, I've found that having too many calculated tables/columns leads to severe performance issues as every time you modify the data model in any way (such as adding a measure, modifying a relationship, etc.) will cause every calculated table/column to re-calculate across the entire model. My personal suggestion is to limit the amount of calculated tables/columns directly in Power BI and try to off-load these structures into a better environment (such as SQL or even Excel if needed). 

[IntaBruce]

That makes sense.  Since I started out on my Power BI journey I have gradually started doing more transformations and things like table joins in Power Query before the data gets to Power BI, maybe I need to go back and look for more opportunities to do that.

[Deku]

If you do your model changes you can bunch a number of transactions together before committing to the model.

You could also consider using parameters to filter tables when developing local, to reduce model size. Then removing when deployed in the service.

You can also consider increasing your RAM as tabular models are cached in RAM