Structuring Power Query for performance

[Carl]

I would appreciate if anyone can give me tips or point me in the direction of resources/books that would help me with the following.
I have a lot of experience with relational database design like MS Access, but am fairly new to Power Pivot and am finding my workbooks are taking a long time to refresh. I've been building some complex models and my latest is taking 10 minutes to update all. I have maybe 30 queries, many referencing each other. I tried to do further calculations and that then took 20 minutes to update and give me an error. I've been trying to keep all my queries in the data model until I need a final output. Is it best to output result 1 to a table and then reference that instead of a query in the data model to calculate result 2?
WHAT IS THE BEST WAY TO STRUCTURE A POWER PIVOT MODEL?

Thanks in advance.

Carl in Sydney

[MattAllington]

Check this out first and see if it helps. https://exceleratorbi.com.au/how-query-folding-works/

If this is not the issue, then it can be quite complex. Post back how you go.

[Carl]

...

[Carl]

Check this out first and see if it helps. https://exceleratorbi.com.au/how-query-folding-works/

If this is not the issue, then it can be quite complex. Post back how you go.

Not ideal for me as I am trying to keep my budget model self contained in one workbook for ease of use by managers. Having to wait 10+ minutes to refresh any change will not be popular. I have 3 main tables as my inputs, expenses, income and salaries, all under 200 rows, and several smaller tables with variables that need to be merged to perform calculations. All this is multiplied by 4 years x 12 months, for a monthly result for all accounts/department and then x2 currencies. I end up with a table of 17K rows that I can reference by pivot table.

Thanks nonetheless as I may have other uses for query folding. I'll try and get my company to send me to one of your courses.

Cheers