Stop workbook refrshing while building powerpivot tables

[masplin]

Is there a way to stop the workbook refreshing while I'm building up my powerpivot model? Driving me nuts that every time I add a calculated column or edit the title I have to wait 30 seconds for the workbook to calculate. These new columns are not in any of the pivot table measures so completely unnecessary. I have turned off automatic calculation in the workbook, but no change. I tried turning off automatic calculation in powerpivot, but that just stops the column calculating while the wrok book still refreshes everything.

I would like to be able to create all my new calculated columns and then just refresh the workbook once. Any idea?

Thanks
Mike

 

[Jacob Barnett]

Mike, assuming you are using 2010 this will help you out.

PowerPivot field list and Excel field list at same time! « PowerPivotPro

 

[masplin]

Hi Jacob. Actually I'm suing 2013. I'mnot sure what seeing 2 field lists has to do with my problem? Simply if I edit the column name of a new calcualted column in pivotpower window and hit enter my workbook runs off refreshing data. The new calculated column has just been created and is not in any pivot table. There must be some setting as it didn't used to do this in 2010 you just got a "refresh button at the topof the field list when you went back to the workbook.

 

[miguel.escobar]

There's no workaround that I know of for this type..just the one that you've used so far for stopping the automatic calculation on the Design tab. What's happening is that the formula is being copy/pasted in every row and that's the only process that should be happening when creating a new calc column. If it's taking too much time it could be that you're using a 32bit version of Powerpivot. wish that I could give you any hint but that's how things are so far :/

 

[masplin]

ah silly me you mean the "defer layout update" bit. Tried turning that on and exactly the same.

 

[miguel.escobar]

ah silly me you mean the "defer layout update" bit. Tried turning that on and exactly the same.

that one actually works but when creating measures in Excel 2010 within the pivot table itself. Just not within the Powerpivot grid/window

 

[Jacob Barnett]

Sorry Mike, didn't read your question as well as I might have.

One thing that Rob Collie talks about in his book/blog is not doing a whole bunch of work building loads of Pivots before you have done the majority of your building of the model/measures.

If you are talking about a workbook that has a lot of reports in it what you can do is make a copy, delete all the tabs, make your changes then reopen your copy workbook and move the sheets from the 'old' workbook into the 'new' one and hey presto it will work!

My experiences in 2010 are that the time taken to process is more to do with it pushing the changes through the existing reports as opposed to be it being driven by actually populating the calculated column.

Obviously its totally crucial that the copy you make is secure!!!

 

[masplin]

It still doesn't make sense that a calculated column that is NOT in any pivot forces a rereading of the data when you just change the title. This is not copying the formula down which I expect.

I'll try the removing the tabs and then reimporting them which I didn't know you can do.

Thanks