Hi,
I'm going through the PowerQuery training using Excel 2010 and I'm on Exercise 4.21. I am trying to access an existing PowerQuery connection I created in a previous exercise. The process I used was...
1. I opened the file of a previous exercise that had a query I created
2. I went to the Data tab, opened the Connections dialog, selected my query and hit the Properties... button
3. From here I went to the Definition tab and hit the Export Connection File... button
4. I saved the file to a location on my PC
5. Then I went to a new workbook and saved it
6. Then I went to the Data tab of my newly saved workbook, selected Existing Connections under the Get External Data section and I browsed to locate the odc file I created in step 4 above
7. When I attempt to open the odc file and elect to open the connection as a table in my existing worksheet, I get the following error: 'The connection could not be refreshed. This problem could occur if the connection was copied from another workbook or the workbook was created in a newer version of Excel.'
Does anyone know what may be causing this error? The version of Excel I'm using is the same for both the connection source and the new file I've created to attempt to open the source. I've Googled on this and have not had any success in finding a solution. I've reviewed my Trust Center settings, but I'm not seeing anything that may resolve this issue.
Any insight is appreciated.
Thanks!
Hi Stuart,
Thanks for your detailed description of your process. I'm not sure why you're getting that error.
Another option is to simply copy the query from the Queries & Connections Pane (which is just called 'Queries Pane' in some versions of Excel). You'll find it on the right-hand side of your Excel window. Select the query and press CTRL+C or right-click > Copy.
Go to your new workbook and right-click in the Queries & Connections Pane > CTRL+V.
Or you can create a blank query then open the Advanced Editor and paste in your query over the top of all the code already in there.
I hope that workaround is suitable.
Mynda
Thanks Mynda,
I saw those options in the other tutorials and those are definitely good for an alternative plan if I can't the original process to work. My reasoning for trying to use the odc file method was that I could easily point other analysts I work with to the odc file and have them refine the query as needed.
I appreciate your reply and if I'm able to figure out the issue, I'll post the solution.
Thanks again,
Stuart
Yeah, I agree the ODC file method is more convenient when sharing with others. You could try creating it on a different PC to see if it's your PC causing the issue.
Mynda
