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.

Power Pivot Rejected by Business Manager

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.

 Power Query reveal column

“Closest Focus”, has now been added.

Save that Power Query.  Open the Power Pivot window, there is only one table, let’s refresh it.

Power Pivot Error After Editing Power Query

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

Power Pivot Corrected Power Query Connection String

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.

 Power Pivot Corrected Power Query 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

 Power Pivot Data Refresh Success After Editing Power Query

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.

 Power Pivot add hidden columns to model

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!

This Post Has 41 Comments

  1. Could you help me resolve an issue with something similar. I connected an excel spreadsheet which resides on the BI server to a data model also on the Bi server. It refreshes fine if I pull the data model down to our machine but fails when I try to setup auto refreshes on the server. Here is the error message.

    OLE DB or ODBC error: Failure creating file.; 3436. A connection could not be made to the data source with the DataSourceID of ’73e87f62-05ac-4ed3-bf5f-8152ef3c82c2′, Name of ‘Excel ATT2014TABLES’. An error occurred while the partition, with the ID of ‘BMS Only_ee478831-c49e-4d64-8525-14bb2e3cfb6a’, Name of ‘BMS Only’ was being processed. The current operation was cancelled because another operation in the transaction failed.

    I tried what you have in the article above but the connection string was exactly the same,

    Any help you can provide will be appreciated.

  2. This is excellent Ted. Yes I have had this issue and never found a solution, and that has made me nervous to use Power Query. Thanks for the tip.

  3. For Excel 2013, with latest Power Query, it seems that if I get a similar error, it tells you to disconnect the Power Query. So uncheck the Load to Data Model. Refresh the query. Then reconnect the Load to Data Model, and the refresh works! After I do it once, it seems to take most of the changes to the Power Query in stride…

  4. Correct me if I am wrong but that solution doesn’t work on 2013 excel. The official answer is that the below reasons can break the query (no turning back ) The solution is to make this changes and actions with power query.

    •Edit Table Properties
    •Column-level changes: Rename, DataType change, Delete
    •Table-level changes: Rename, Delete
    •Import more tables using PP Import Wizard
    •Upgrade existing workbook.

    After applying all the above I never had any problems with the data model. Any change in power query doesn’t affect calculated columns or calculated fields.

  5. I’m afraid this doesn’t work with the newest version of Power Query Version: 2.14.3722.242. Doesn’t seem to support loading to the Data Model at all… Please let me know if anyone has a workaround.

  6. I discovered that as long as you load it to the worksheet once you can then subsequently edit the query, uncheck load to worksheet, and then follow the steps in the post above. In cases where the query returns a huge table it works to simply add a step at the end of the power query that removes all but the top 2 rows, load to worksheet, create the power pivot connection, edit the query to remove that last step of the query, then edit the connection as described above. Hope that helps.

  7. This is killing me. When I try the above approach, I can modify the the connection in the advanced dialog, and the test connection succeeds. But once I return to the Edit Connection dialog and click save, I get an error saying I need to provide a database name.

  8. I’m changing my approach. Step 1: load Power Query results to worksheet. Step 2: load Power Query results table as a linked table to Powerpivot model. Seems to work so far. Any reason this is a bad idea?

  9. Linked table limitations:
    1) limited to <1M records
    2) refresh is not consistant. I often can't get a linked table to refresh untill I open the PowerPivot Window. Not something I want to force my users to do…

  10. Thanks a million for your solution – works like a charm on Excel 2010. I hope MS BI tools end up making these kind of esoteric undocumented solutions unnecessary…

  11. This no longer works after the November updates to Power Query – Design>Table Properties is no longer an option.

    This is super frustrating and makes me not want to rely on Power Query if I can’t simply add a new column that appears in the source table. Agility is essential to BI and by nature a highly iterative process, and the amount of rework you have to do in Power BI if you don’t meticulously plan out execution steps makes things difficult.

  12. I found a work around. Create the query you need where you apply all your transformation, filters, etc, but don’t load the data just create the connection. The create a query from scratch with the following code

    Source = Table.Repeat(,1)

    Where is the query you created with all the transformation. This way you can do all the changes you need in the first query while the second one all it does is load the data into the model.

    1. Muchas gracias Jorge! This solved a major problem for me.

      I created a Power Query query to pull my source data. I called this query “qrySourceData”. When I clicked ‘close and load’, I selected create connection, but do not load. I then created another query called “qryPowerPivotFeed” with a definition of “let Source = Table.Repeat(qrySourceData, 1) in Source”. I then loaded this query to my PowerPivot model. I could then change the qrySourceData query and hit refresh and it didn’t break my PowerPivot model.

      1. …on further review, I was able to modify qrySourceData to the point where PowerPivot balked and failed to load the Power Query result to the data model. The error message said something like “qrySourceData or one of it’s sources changed…”. I guess I’ll tread lightly and hope for the best. If I figure out what exactly caused the issue, I’ll report back.

  13. Jorge, could you please elaborate a bit more (details) of your workaround? I have 5 tables in my model all created with Power Query, and more than a handful of measures. I only need to change a filter in 3 tables – for my mistake, it is not bringing current day data. I try everything I could imagine but after reconnecting to the model and even re-customizing the powerpivot tables, remaking the relationship, nothing works. All reports are blank. Thank you.

    1. Hi,

      Since you already created the model based on the PowerQuery you are going to have to buy the bullet and re-do the measure and the other things you did in the data model.

      What you have to do is, don’t change your current power queries. You need to create new ones with the code I placed above to basically create a copy of the output. That new one is the one you use to upload to your data model and then create your measures. In the future, you don’t touch the new ones just the old ones in case you need to make any changes.

      For your queries, don’t load them just create the connection that way you don’t increase the file size.

      I hope this can help

  14. I have read Jorge’s tip above, and Phil’s experience when he thrashes that approach. Sounds like a big improvement – maybe Microsoft could look at why this improves things as part of the process to fix the problem.

    For what it’s worth, the approach I now take with Power Query is simply to NEVER create any measures and place them in a PQ table. Instead I create a new dummy linked table (1 row, 1 column) and add all my measures that would normally go in the PQ table and put them in this dummy table. I get a lot of false warnings on data connections, but if (or when) the PQ link explodes, at least I just have to remove and add the table back, and then rejoin the tables. This is a 1 minute job. When this has happened to me with this approach, I count my blessings that I took the wise decision to add a dummy table.

  15. I had same issue. Here is my resolution:
    1) Open Power Pivot
    2) Delete the table that was created by “original” Power Query process before it broke using “modified” process
    3) Reimport data to Power Pivot data model using “modified” Power Query process

    Solution based on statement broken Power Query process “will always happen anytime we make any kind of change to a Power Query that is connected to a Power Pivot table.”

  16. I have created “qryPrj” with actual Oracle Query and haven’t loaded the data into datamodel
    Created another blankQuery “PrjAct” as suggested by Jorge and refered “qryPrj”. Loaded “PrjAct” to data model. I have modified the filter conditions in the “qryPrj” and able to refresh the “PrjAct”.

    Once i changed the column names in the “PrjAct” (refrence query) and try to reload – refresh broke. . (Exception from HRESULT: 0x80020009 (DISP_E_EXCEPTION))

    We have created about 10 queries and created a relationships and measures in most of them. Now I’m unable to refresh the data updated/latest data to these tables. Any help is appreciated.

    1. I have removed all the tables in my model and had to re-create all the tables, added the relationships and measures. But this time I made sure if I need to change any datatype or re-name the column name – I do all these changes to powerquery instead in powerpivot.

      Now I’m able to refresh without any issues.

  17. For Excel 2010, as of May 29, 2015, with the most recent Power Query version, Ted’s approach works.

    For me, I deleted a column instead of adding a column and after following the directions in this post, I received a message that my missing column could not be found. So I went to Table Properties, but could not find my column to “uncheck”, so I de-selected all, and re-selected the columns that I wanted (then hit Save), then everything was fine.

      1. The most recent Power query did not resolve the issue. The issue was that I renamed a column from the data source which is a table from the same worksheet. The power query failed because it couldn’t perform a replace value on the column that the column name was changed. Even after changing the query formula, the data model couldn’t be refreshed. I had to redo all pivot tables, measures, and calculated columns.

        I’m not sure how power query is worth the time lost fixing the data model after very minor changes. Microsoft needs to fix these issues with power query, data model, and powerpivot.

          1. I understand the cause. When I change the type of a column in power query its not reflected in the data model. All formatted columns in power query become text in powerpivot and if I change anything in powerpivot, the connection becomes read only.

            Any suggestions?

  18. Jorge,

    When I use your method, I get a new query that has no output. It just shows the code you used in one row within PowerPivot. What am I missing?

  19. Access was so easy to understand and allowed you to quickly develop powerful data tools (I have used Access for 20 years). This PQ PP is so convoluted in concept alone that I am beating my head against the wall just to understand the basics!! Next layer on all these glitches and crashes and you have to wonder who is coming up with this crazy logic (If you cal call it that)!

    1. Glenn, I totally hear ya. And really wish things were better. Well, they have gotten a lot better and are getting better, albeit too slowly in some cases. You’re right, Microsoft has a big ship to run, but that doesn’t excuse them from paying attention to the customer. While everything is not perfect, I do feel the Power BI team under the dynamic leadership of James Phillips is just kick-ass! The industry seems to agree – Microsoft jumped in the Gartner BI Quadrant report. See Tweet.

      If you have not tried yet, give a chance to Power BI Desktop. Yes, if you’re an Excel lover (like I am) you feel out of place, but it runs a whole lot smoother and has almost no rought edges between the various tools (Power Pivot/ Power Query/Reports or Power View).

Leave a Comment or Question