power Pivot data model question

Hi All,

I'm using the datamodel feature to create a pivot with four years of data. I'm linking four excel files (one for each year)to a chart of accounts file. The COA file has 15 tabs, but i'm only bringing three tabs - departments, FTE counts, and funds into the model. I've have great success with this methodology until my coworker realized that the chart of accounts file needed additional columns, but didn't tell me, so when I copied the new COA over the existing COA I ended up with data load errors.

In short, the original tabs had 5 columns and the new had six columns, with the new column inserted in the middle. As a result, it blew the pivots tables.

Is there anyway to refresh the one table in the design mode and not have to delete the old table and relink to the new? I know when I've used the get data feature and I change the order of columns in the source file, I can edit the source query, so i'm hoping there's something similar.

thanks.

I think I understand your dilemma. In the diagram view of Power Pivot, you can refresh a single table and it should retain your existing connections and relationships. Click on the table in the data model and use 'refresh'. The column headings will update, but won't change your connections. You may need to delete or edit your connections then, depending on which relationship is changing or not.

thanks, took the weekend off.

I ended up deleting the two data connections and recreated the connections, which then brought in the two new columns. I just couldn't get the new column field names to appear, unfortunately then I lost the pivot tables I had created fortunately there were only a few.

I'll check your your solution

I recreated the issue, but that didn't work either.

I resolved the connection issue. I highlighted the department table in the power pivot design window. Then I selected table properties and added the new columns.

It automatically appeared in the department table list,

but now if i try to modify the workbook connected, i get: Failed to connect to the server. Reason: Could not find installable ISAM. The data source is an excel file, not access. Any ideas how to fix this?