PowerPivot calculated fields not refreshing until Pivot interaction

[cr731]

I'm using Power Query to retrieve data and then load that to the data model.

From there, I have some calculated fields to do basic things like the sum of columns.

I've noticed that when I refresh the data, my PivotTable is not refreshed until I interact with it - such as by expanding/collapsing a field.

I checked in the data model and the calculation mode is automatic.

I've never noticed this behavior before... could the workbook be corrupted or is there some setting that causes this?

 

[Matt Allington]

What version of Excel are you using? 2010 can be problematic and may need this behaviour. if it is 2013, then it doesn't sound right to me. You could try installing OLAP tool extensions, turning off auto update and then turning it back on. See if that helps.
https://olappivottableextend.codeplex.com/


Also I find sometimes if you add a calculated column (just something like =1) and then switch back to Excel, it can force a hard refresh that sometimes "fixes" things. If it works, you can just go and delete the calc column later

 

[cr731]

What version of Excel are you using? 2010 can be problematic and may need this behaviour. if it is 2013, then it doesn't sound right to me. You could try installing OLAP tool extensions, turning off auto update and then turning it back on. See if that helps.
https://olappivottableextend.codeplex.com/


Also I find sometimes if you add a calculated column (just something like =1) and then switch back to Excel, it can force a hard refresh that sometimes "fixes" things. If it works, you can just go and delete the calc column later

I'm using Excel 2013.

I tried deleting and recreating the calculated field, and also tried your suggestion of creating (and then deleting) a calculated column of =1 ... between the two of those, it now works correctly. Thanks for the tip.

 

[cr731]

I spoke too soon; the issue is not resolved.

Upon further examination, it appears as though when I refresh a PQ query, the ThisWorkBookDataModel connection is not being refreshed (I check the Last Refresh time stamp in Data > Connections) and it isn't refreshing.

I tested a few other workbooks and ThisWorkBookDataModel does refresh upon a PQ refresh (assuming the PQ loads to data model, which both do).

Any experience with this issue or how to resolve?

 

[Matt Allington]

No, never seen it. Are you refreshing Power Query or "refresh all"?

 

[cr731]

No, never seen it. Are you refreshing Power Query or "refresh all"?

I've tried both.

If I manually refresh ThisWorkBookDataModel after the Power Query refresh (or after Refresh All), it updates... but I'm not sure why that isn't being refreshed automatically when I Refresh All.

 

[Matt Allington]

Certainly sounds like some sort of corruption. Maybe you could try saving the workbook as XLSB and see if that helps.