Basically there is some contention between the Power Query (PQ) queries and the Pivot Tables whilst pointing to the same Power Pivot data model.
All the Power Pivot tables have PQ's as their data source, and all PQ's have CSV files as their data source.
There is an auto data refresh process which is triggered from Pivot Table which needs to be disabled. Also for added insurance the Power Query queries need to be disabled when we don’t want to explicitly refresh the Pivot Table / PowerPivot model from the
input sheets.
More Technical Detail
The evidence of this is as follows; -
1) Refreshing the Power Query query DimYear seemed to either freeze or take in access of 5 minutes where DimYear returns a single record!
2) I deleted all Pivot Tables, and made sure all the Power Pivot table relationships were correct.
3) Refreshing the Power Query query DimYear completed instantly as expected.
4) I attempted to rebuild again the Pivot Table from scratch. The Pivot Table became too slow. Dragging another calculation into the Pivot Table took over 45 seconds once; - all the attributes were included and the first 10 calculations were included. There
was only one record within each of the input sheets, so only one record within the Pivot Table output. So this is certainly not a record volume issue.
5) After the attempt at rebuilding from scratch the Pivot Table I noticed that the DimYear Power Query query took a full minute to execute.
Next Steps
1) Create a new copy of the solution and delete all the Power Query queries.
2) See how the Pivot Table performs after the removal of the Power Query queries.
Thanks in advance,
Kieran.
Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/
