Pivot Table lost Connect after changing Power Query

[DMfba]

Need some suggestions on how to repair the connection to the data source after updating Power Query. Typically I would pull in data from a folder off a server, however this is my first time making a connection through an Access database. I have pulled in several data sets from an access database and one from an excel file and then merge the queries together to get the final table that I need in order to start making pivot tables.

What I noticed, if I have to go back and change anything in Power query I lose the Pivot table. For example, if I get asked to include a field that was previous deleted, or If I need to make a customer column in power query Editor, after closing the pivot table is now gone. The error message comes up that excel lost the connection to the data. When I click the data connection window everything is blank.

Is there a way to correct this issue or a possible work around for the times you need to go back and alter the process? In the past, when I connect directly to a folder I never had this issue. So far, I have been forced to recreate the pivot tables.

Let me know if anyone has encounter this before. All of my online searches seem to only pull back issues with Power Pivot or Power BI

 

[sandy666]

Data Source Settings - check if your source is correct, if not choose correct source then refresh PQ

 

[DMfba]

That is the issue. After any changes in Power Query, specfically anything that was pulled from one of the Access database queries or the merged queries, those connections are no longer there. I am looking for a way to keep those connections intact or a way to restore them. Clicking on the proper connection inside of Data Connections was my first thought, but they are not there. However, that would bring up another question, if they were how do we set it for them not to be disconnection on every change?

 

[sandy666]

I said Data Source Settings not Data Connection

or load temporary table from PQ into the sheet and it will should reactivate connection

 

[DMfba]

Thank you for pointing that out I misread your comment. Sorry for the confusion. I went in and re-selected the correct links, however it did not correct the issue. To be on the safe side of things, I checked both the link when the data was imported and the link for the merged query. The data set is large enough where I am not able to load the table into the workbook directly. Could this be a permission issue and not a linking issue? If the connections are correct in the Query Editor but are lost in the pivot table, is there another way to correct this issue?​

 

[sandy666]

Could this be a permission issue and not a linking issue?

I don't think so...

if data is too big to load to the sheet you can try create new PT from QueryTable. Of course if you see that QT here:

if not you can try load QT into DataModel and recreate PT from there but.... it will increase size of the workbook

 

[sandy666]

or you can try filter your QT to get less rows, eg. 1000 then load (temporary) to the sheet, then create PT from filtered QT and remove filter from QT and refresh PT
Delete temporary table from the sheet