Wednesday, April 20, 2011

How to update your PowerPivot Field List after a change in the datamodel?

It can happen that you have made a nice PowerPivot dashboard with a lot of usefull pivots and charts. After a while some changes are made in the datamodel of the database on which you have build your PowerPivot dashboard. For instance one column is added to an existing table or view. In this blog I will describe how you can update your PowerPivot Field List with the new column, so you can make use of it.

  • Open the Excel sheet with your PowerPivot dashboard.
  • Select PowerPivot tab in the Ribbon.
  • Press the button PowerPivot Window button to launch the PowerPivot window.
  • Select the table or view on which a column is added
  • Press the button Table Properties.
  • Be sure to select Column names from Source.
  • Scroll to the right. At the end you will see the new added column. Select this column. In this example Zipcode
  • The added column ZIPCode is now added to the PowerPivot Window.
  • Select the Excel sheet with you rPowerPivot dashboard.
  • Show the Field List.
  • The PowerPivot Field list has detected that a change is made. Press the refresh button.
  • Select the Data tab in the Ribbon of your Excel sheet and press the refresh all button. This update your excel sheet with the information of the added column.
  • Now look in the PowerPivot Field List and you will see the added column in the table or view.

Enjoy using your PowerPivot dashboard with the new column(s)


Unknown said...

Hi Andre,
Thanks for taking the time to help other users out there. Much appreciated.

I am trying to find a way to add additional columns via the 'Paste Replace' method. I am wondering if you would know how to achieve this?

More info:
1. Original data set was pasted directly into a powerpivot table. (I don't know if this is best practice)
2. My data source has since been modified and I need an additional column of data
3. If I add a column and try to 'Paste Replace' I get the error that number of columns pasted doesn't match


Daniel Lisak said...

Hi Andre

today, almost 12 years after you had posted this, it saved my day!

Thank you very much