Hi Melinda,
First of all thanks for your great course material. I learned a lot from your DAX and power pivot courses, and of course your excel course as well.
I am running into the following problem:
I created a pivot table in excel based on power pivot. Because my excel file got corrupted, I saved the file as a new version with a new name. However, now I find that my pivot table remains connected to the power pivot model within the OLD excel file. (see screen print attached)
I cannot seem to get rid of this connection. When I delete it using the "Edit connections window ("Querys en verbindingen, see attached screen print)", and subsequently try to create a NEW data model in my new excel file by adding my tables again to power pivot, then when I create the pivot table I see again the old tables from the old power pivot model. The old powerpivot data model and the new power pivot model seem to be interconnected somehow. Is there a way to clean up this old connection to the old power pivot?
This is very frustrating, so I hope there is a solution for this!
Thanks!
Update: By trial and error, I have managed to get rid of the connection to the old power pivot model now, it seems. 🙂
But I still would like to understand the cause of this and how to handle it more elegantly (since this is not the first and probably not the last time I am running into this).
Does this problem occur when excel does not exit properly and the connection to the power pivot model is not managed properly somehow?
Hi Linda,
Glad you found a solution.
I expect that your old PivotTables would need to be deleted to get rid of the connection to the old data model. Then re-build them from scratch from the new data model.
To avoid this in future, start a new file and don't copy any PivotTables across. PivotTables always retain a connection to the original data source.
Note: you posted your question in the Power Pivot public forum. I have moved it to the private course members' forum. That way, if you post any follow-up questions, I'll see them and be sure to answer promptly.
I hope that helps.
Mynda
thank you for your quick reply!
I just have one follow-up question. Does this mean that it is not good practice to rename an excel file that is connected to powerpivot? Because it seems that the connection to the powerpivot is hardcoded based on the file name, and does not get changed accordingly, when I rename the excel file.
In theory, you should be able to save a copy of the file or rename the file and the queries and data model and connected PivotTables all relate to the new file/file name.
However, there is a known issue with PivotTables that sometimes they retain connections to the original source. It sounds like when your file became corrupted the PivotTables got confused about where the source of their data should be, or as a result of this issue, your file became corrupted.
Ideally, you should use Power Query to load your data to the Power Pivot data model. This should give a more robust and stable file when saving copies and renaming them.
Mynda
thanks, that clears things up!
my source tables are already in excel (they are an export from our ERP system), that is why I load the data directly from excel into power pivot.
But power query is a better way to do this then? (I haven't used power query yet)
Power Query offers a more efficient method for loading data into Power Pivot. If you load data already present in your Excel workbook into the Power Pivot data model, it results in duplicating the data within the file. However, by using Power Query to retrieve data directly from ERP system export files, you can connect and import the data into your model seamlessly, ensuring it only exists once in the file.
Additionally, Power Query allows you to clean the data and add calculated columns before loading it into Power Pivot. This leads to a more streamlined and efficient data model.
If your data doesn’t require cleaning or transformation, this may not impact you directly, but avoiding duplicate data in your file is a significant benefit.
Thank you!
Unfortunately for the moment I am stuck with a situation where I HAVE to have the ERP download the data to excel first (this is how our ERPs report generation module works).
But this is useful information for the future, where hopefully we will work with a different reporting solution.
Thanks,
Linda
That's ok. You can download the data to one Excel file and then connect to that file with Power Query from a separate file to bring the data into the data model, that way the data is only in your report file once. i.e. in the data model.
