Pivot Table lost Connect after changing Power Query
Results 1 to 7 of 7

Thread: Pivot Table lost Connect after changing Power Query
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Pivot Table lost Connect after changing Power Query

    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

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,675
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Pivot Table lost Connect after changing Power Query

    Data Source Settings - check if your source is correct, if not choose correct source then refresh PQ
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    New Member
    Join Date
    Mar 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot Table lost Connect after changing Power Query

    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?

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,675
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Pivot Table lost Connect after changing Power Query

    I said Data Source Settings not Data Connection



    or load temporary table from PQ into the sheet and it will should reactivate connection
    Last edited by sandy666; Mar 26th, 2019 at 03:03 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    New Member
    Join Date
    Mar 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot Table lost Connect after changing Power Query

    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?

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,675
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Pivot Table lost Connect after changing Power Query

    Quote Originally Posted by DMfba View Post
    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
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,675
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Pivot Table lost Connect after changing Power Query

    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 by sandy666; Mar 26th, 2019 at 06:19 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •