Pivot Table lost Connect after changing Power Query

DMfba

New Member
Joined
Mar 21, 2019
Messages
25
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
Data Source Settings - check if your source is correct, if not choose correct source then refresh PQ
 

DMfba

New Member
Joined
Mar 21, 2019
Messages
25
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
I said Data Source Settings not Data Connection

datasource.jpg


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

DMfba

New Member
Joined
Mar 21, 2019
Messages
25

ADVERTISEMENT

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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:

existingconnections.jpg


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

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top