Structuring Power Query for performance

Carl
Posts: 3
Joined: Tue Dec 04, 2018 2:08 pm

Structuring Power Query for performance

Postby Carl » Tue Dec 04, 2018 3:44 pm

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
Posts: 1007
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Structuring Power Query for performance

Postby MattAllington » Tue Dec 04, 2018 5:42 pm

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.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Carl
Posts: 3
Joined: Tue Dec 04, 2018 2:08 pm

Re: Structuring Power Query for performance

Postby Carl » Thu Dec 06, 2018 10:12 am

...
Last edited by Carl on Thu Dec 06, 2018 1:09 pm, edited 1 time in total.

Carl
Posts: 3
Joined: Tue Dec 04, 2018 2:08 pm

Re: Structuring Power Query for performance

Postby Carl » Thu Dec 06, 2018 1:08 pm

MattAllington wrote: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


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 3 guests