Opening a Power Query based Excel 2010 worksheet in 2016 Getting Load to worksheet failed- Could not determine workbook that contains this connection RRS feed

  • Question

  • Have a worksheet with 29 PQ Queries. The sheet is Ok on both Office 2010 and 2013. Using  PQ versions Version: 2.40.4554.161 64-bit and Version: 2.39.4526.123 64-bit. We have been exploring moving on to Office 2016, I upgraded the Get and Transform to the latest version of Office 2016 Jan 3 release excel2016-kb3141472-fullfile-x64-glb. Opening the same sheet gives me the following error while updating: 

    The query table couldn't be refreshed: The connection could not be refreshed. We could not determine the workbook that contains this connection. Please try refreshing this connection again in a moment.

    This happens to all the queries :( . Looks like something is seriously wrong, I even clicked the frown button. Is it that Get and Transform somehow looses the connection to the worksheet or it is not able to read the connection established in 2010/2013 Office PQ.

    Need a solution to this as soon as possible


    Friday, January 13, 2017 10:11 PM


All replies

  • PQ in 2010 doesn't play well with power pivot. I think you need to determine where the problem lies, then work out a soulition. What is the destination of the PQ queries in the workbook? Is it Power Pivot or something else? What happens when you open on elf the queries? Can you go back brought the steps starting with the first step and connect to the data source?

    Self Service BI Expert using Power Pivot

    Saturday, January 14, 2017 1:59 AM
  • No power Pivot involved. I load the data on to the worksheet as a table. Loads ok on the worksheet in 2010 but in 2016 the connection between Get and Transform (2016 version of PQ) and the worksheet table is lost!

    It does not matter if the query is sql or workbook same issue. Wonder if there is a library missing in 2016. The bad part is there seems to be no way is PQ to see the code behind load to worksheet i.e. no way to edit that load to worksheet connection. This would have helped debug.

    Tuesday, January 17, 2017 4:12 PM
  • Well it certainly could be a software bug.  There is a new build that is being prepared at the moment but it is hard to know if it will solve your problem.  Can you try to isolate the problem by looking at 1 query?  What happens if you cut and paste from Power Query 2010 and paste into a new blank query in 2016?  What happens if you paste into Power BI Desktop?  I suggest pick one query and try to isolate the issue

    Self Service BI Expert using Power Pivot

    Tuesday, January 17, 2017 9:03 PM
  • I think I figured out what is going on the problem seems to be the Data Source Settings when I use office  2010/2013 when I go to the connection I see under Native Database Queries I have approved 1 Native Query For this source 

    Now opening the same workbook in Office 2016 and the same connection

    You see Native Database Queries have not been approved, the Privacy level went from Public to none. There seems to be no way in GT to approve Native Database queries. Looks like in 2016 approval within the Data sources within the workbook did not come over. Question now is how do I go about approving Native database queries in 2016.

    Tuesday, January 17, 2017 11:39 PM
  • you can turn off privacy levels if you want (power query options), but that will mean it wont warn you.  If you are ok with the risk then that should fix it.

    Otherwise I think if you manually run each query you will be prompted for the privacy level.

    Self Service BI Expert using Power Pivot

    Wednesday, January 18, 2017 3:45 AM
  • Hi Chaitanya, can you share your Excel build number?

    Also, can you see if you have the "Analysis Toolpak - VBA" add-in enabled, and, if so, try disabling it to see if it resolves your issue?



    Thursday, January 19, 2017 10:20 PM
  • The Build number is Excel 2016 16.0.4266.1001

    I will try and disable the analysis tool pack -VBA addin and see if that helps

    Wednesday, February 22, 2017 8:43 PM
  • This worked for me. Thank you.
    Monday, January 21, 2019 1:29 PM