Guest Post by Ted Eichinger
Note, this fix to re-establish a broken connection is performed using Excel 2010
It’s the same old story, I mashed and twisted some data through Power Query, pulled it through Power Pivot, spent hours creating calculated columns and measures, made a really nice Pivot Table with conditional formatting and all the bells and whistles. Then I show it to the Business Manager that requested it, and he wants me to add a field that I didn’t pull in through Power Query. I’m going to have to change my Power Query which is going to break my Power Pivot connection, and then I’ll be spending hours to rebuild my model from scratch!
This happens to me more that I’d like. I’ve searched the far reaches of Google, only to find a lot of threads asking about how to fix, but I’ve never seen an answer. Most threads end with no, you just can’t do that. Just playing around on a hunch, I figured out a way to fix it without having to rebuild the model!!! Some already know the “fix”, for those that don’t , I’ll walk through the method that’s been working for me.
If you want to follow along download the file below, I’ve used some public data on camera lenses so anyone can refresh the data model (use Anonymous access if prompted), or follow along just using the pictures.
Yes You Can Example.xlsx
In this example a Business Manager wants me to add the field, “Closest Focus” to the Power Pivot table.
This is the Power Pivot table that requires an additional field
This should be easy, just open the Power Query (in the example file) named, “Macro photography lenses”, and delete the “RemovedColumns” step and the “Closest Focus” column should now be back.
“Closest Focus”, has now been added.
Save that Power Query. Open the Power Pivot window, there is only one table, let’s refresh it.
The dreaded Power Pivot error!
This will always happen anytime we make any kind of change to a Power Query that is connected to a Power Pivot table. If you’re working with Excel Power BI tools you’ll eventually see this error. The error detail reads:
The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source.
More Details:
OLE DB or ODBC error: The query ‘Macro photography lenses’ or one of its inputs was modified in Power Query after this connection was added. Please remove and re-add the connection. This can be done by disabling and re-enabling download of ‘Macro photography lenses’ in Power Query..
The FIX!!
To fix this error, in the Power Pivot window, Go to Design > Existing Connections
Under Workbook Connections select the Power Query (in this workbook, Power Query – Macro photography lenses)
Click Open
This is the “new” corrected connection string
Copy the Connection String
Then click Cancel, we don’t want to re-import that table again.
I think this is where the magic happens. If I’m thinking this correctly, when I changed the Power Query, I changed the connection string, so this is a copy of the new (corrected) connection string.
Then go back to Design > Existing Connections
Now we go under PowerPivot Data Connections and select our broken Power Query connection, and then click Edit
In the Edit Connection dialog window, delete the old connection string and paste in the new connection string.
This is the message you should see after you have pasted the new connection string
When you’re done a message will appear in the bottom dialog, click Save.
Close the Existing Connections Window
Go back to Home > Refresh
Data Refresh dialog should appear with the Success message
… And then everything is AWESOME again!! I see Emmet dancing around and I hear that song from The Lego Movie playing in my head.
And everything is back to the way it was, I don’t have to spend hours to re-build my model, even the pivot table is intact!
Wait…. One more step before everything is truly awesome! The new field that I added to Power Query is now missing in my Power Pivot model.
Easy to fix Missing Field(s)!
Go to Design > Table Properties
“Closest Focus” is not checked, just check it.
Just make sure all columns you want visible in your Power Pivot model are checked
Click Save, and NOW everything is awesome!
And here is what Emmet has to say!
Get in touch with a P3 team member