none
Modification to Power Query results in Power Pivot error upon refresh

    Question

  • Any time I modify a query in Power Query, refreshing the Power Pivot table that uses the Power Query Workbook connection fails with the following error:

    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 'Table1' 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 'Table1' in Power Query..

    An error occurred while processing table 'Table1'.

    The current operation was cancelled because another operation in the transaction failed.

    The error message says I can remove and re-add the connection by "disabling and re-enabling download" but I don't see any way to do this in Excel 2010.  Plus I don't want to destroy any measures or relationships in Power Pivot.

    fyi - I am using Excel 2010 so obviously I had to use "Existing Connections" to select the Power Query table workbook connection to load into Power Pivot.  Also note that I do not load Power Query to worksheet. 

    Any ideas?

    Friday, March 07, 2014 7:12 PM

Answers

  • The issue you are hitting is the one we are trying to fix in Excel 2013. The fact that you have to disable/re-enable the connection is causing your data to be lost in PowerPivot.

    In Excel 2010, we don't support working with the data model at all.

    Thursday, March 13, 2014 11:33 PM

All replies

  • Did you make any additions or modifications in PowerPivot as well, for example renames, or adding new columns? Could you please describe your steps exactly so that we could try to reproduce it?

    Thanks


    Zafar Abbas

    Sunday, March 09, 2014 3:37 AM
  • Hi Zafar - no I didn't make any additions or modifications in Power Pivot.  All I did is create a Power Query query, go to Power Pivot window, click existing connections, select the Power Query query, confirmed that the query was loaded into Power Pivot window.  All is well so far.  Refresh Power Pivot window.  All is still fine.  Now, go back to Power Query and make any modification to the query, such as sort columns, or filter, or insert a new custom column.  Then come back to Power Pivot window and select refresh.  That's when I get the error.
    Sunday, March 09, 2014 4:16 AM
  • I am able to repro your issue using Excel 2010. The problem here is PowerPivot capabilities of O14 are vastly different to those of O15. That is the reason why we don't support loading to the Data Model in O14.

    I have filed a bug with your repro and I'll follow up with my team about this issue.

    Thanks for your feedback!

    Rodrigo Aceves

    Monday, March 10, 2014 9:57 PM
  • What is "O14" and "015"?  Is this something I can update in Excel 2010?  thanks so much for the feedback!

    Tuesday, March 11, 2014 1:04 PM
  • O14 = Office 2010
    O15 = Office 2013

    Wednesday, March 12, 2014 5:26 PM
    Moderator
  • I have the same problem with Excel 2013 .

    I have a MySql query set up in power query (I haven't tested in latest power query -March 2014) . After changing the query (adding more columns - i believe), I get the same message.  I am able to disable and reenable download, but then all my measures & relationships are lost in Power pivot.

    At the moment I am considering whether to

    a) record keystrokes to "back up my measures" to a text file  

    b) use power pivot only and its data connections... though this seems a backward step. In particular I want to run a parameterised query(to only load last x weeks of data from main table, and then corresponding data from related tables). It apparently can be done in vba...

    [Since I am developing the query and the analysis iteratively, this is very annoying!]
    • Edited by seanvio Thursday, March 13, 2014 12:21 AM
    Thursday, March 13, 2014 12:20 AM
  • Seanvio - wow, you have this same issue in Excel 2013?  This seems like a pretty big bug that makes me want to discard Power Query.  To not allow the flexibility to change the Power Query without destroying the measures and relationships in Power Pivot seems like a deal breaker to me...  And I know that in our corporate environment it'll be years before we ever move to Office 2013.  such a shame - was really looking forward to using Power Query...

    Thursday, March 13, 2014 1:21 PM
  • I aim to try to reproduce it on eg adventureworks  database maybe I just have to avoid renaming in power pivot ( as zafar asked)???
    Thursday, March 13, 2014 4:22 PM
  • About discarding measures and relationships after disabling and re-enabling download: We are aware of the issue and currently we are working across all the teams involved to fix it. It is currently high in our priority list and it should be fixed very soon. I know it's not ideal, but try and make the changes to the model after all the renaming operations in Power Query are done.

    Thanks for your feedback, we really appreciate it and we are working hard to make things better!

    Thursday, March 13, 2014 6:22 PM
  • rodrigo

    Do you confirm that there is the same issue in 2013? I wanted to make sure that it is not my "user error" - eg as long as I don't do X everything will work.  Because your previous answer seemed to suggest it was only an Excel 2010 issue.

    Thursday, March 13, 2014 7:04 PM
  • How do you add the Data to the Data Model? Are you adding a new column in Power Pivot or in PowerQuery?

    Thursday, March 13, 2014 9:16 PM
  • well I did both. I had added columns in power pivot. then I added more entries in my mysql table , edited the power query and tried to load from mysql via power query again. But as I say I haven't got a clean example yet- it just happened through my work.

    But I   get the error after reloading data from power query. The error message is slightly different from rickthoward:

    Outof line object 'DataSource' referring to IDS 'xxxxxx' has been specified but has not been used. OLE DB or ODBC error: the query 'sqlHistory' or one of its inputs was modified in Power Query after this connection was added. Please remove and re-add the connection....

    Thursday, March 13, 2014 9:39 PM
  • The issue you are hitting is the one we are trying to fix in Excel 2013. The fact that you have to disable/re-enable the connection is causing your data to be lost in PowerPivot.

    In Excel 2010, we don't support working with the data model at all.

    Thursday, March 13, 2014 11:33 PM
  • I had the same problem, also using Excel 2010. What I do (still have to do it):

    1. Create Connection in powerpivot using your power query via Existing connection. (you have already done this, just stating the step for anyone to try and replicate if they wish)

    2. close Powerpivot, open Power Query and make changes to Query. Refresh query and apply and close and save workbook (no need to close) (repeato ditto, this is what caused your problem)

    3. To fix the problem - Reopen Powerpivot and select existing connections. Go to Workbook Connections and select the query you have modified. Select Open and you should see the connection string. copy the whole string and then hit cancel

    4. Then go to your PowerPivot Data Connections and select the connection that used the query. Select Edit and delete the existing connection string. then paste the connection string you copied previously. Hit Save to save the pasted connection string.

    5. close the existing connections form, save the workbook and then refresh your powerpivot data. Should work fine.

    It is a bit tedious but it does save by powerpivot table so I don't have to redo all my Dax. 

    couple of common errors I get 

    1. Run out of memory, you have a 32 bit version, change to 64 bit blah blah. Just close your workbook and reopen and try again. should work fine.

    2. unable to create connection when opening workbook. Just ignore it, only a timing issue

    3. Data is corrupt, resave your file, fix errors, unable to recover and all that rubbish. save the file as a different name. if you can't then close powerpivot and power query windows and try again. if you still can't then reopen the powerpivot window. move through a couple of tables, close powerpivot and try to save as again. should work this time.

    Hope the above helps. :)

    Dale


    Tuesday, March 25, 2014 3:49 AM
  • Dale - Thanks!  That works perfectly.  It may be a little "tedious" but it is a heck of a lot easier than recreating the model.  'So thankful for that tip!
    Thursday, March 27, 2014 12:54 PM
  • Is there any update on this?  I have run into the same issue in Excel 2013 64-bit.  Thanks!
    Thursday, April 10, 2014 3:06 PM
  • I was finally able to get my issue resolved easily in Excel 2013.  After you modify your Power Query, uncheck the check box at the bottom that says "Load to Data Model".  Apply & Close your Power Query.  You will see in your Workbook Queries, that the query is listed as "Disabled".  Open the query again, this time, check the box and then Apply & Close.  Everything refreshed as expected. 

    Hope this helps others.

    Thursday, April 10, 2014 3:19 PM
  • @Malinda

    Yes, that is what the error message tells you to do. The problem is that you then lose all your work in power pivot ( added columns, calculated fields etc) as well as all the resulting pivot tables and charts you might have made.

    Thursday, April 10, 2014 9:25 PM
  • In Excel 2013, not every change in power pivot/power query causes problems.

    AFAIK it DOES NOT  occur when you reoorder columns in PP (power pivot) and PQ (power query), or add new columns in PP or PQ

    AFAIK the problem occurs when you modify a column in power pivot, and then also change the same column in power query.

    eg

    a) change type in power pivot from text to number then  rename column/change type  in PQ

    b) rename column in PP, then rename/delete same column in PQ

    So one work around is to  make all changes to columns in PQ, and leave PP for adding measures then you will be fine.[ I think you should be fine if you add columns to PP too]

    I have been  using this query entering city and your yelp api as appropriate (see  http://blogs.msdn.com/b/powerbi/archive/2013/07/07/getting-started-with-pq-and-pm.aspx) as my test case

    let
        Source = Json.Document(Web.Contents("http://api.yelp.com/business_review_search?term=seafood&location=YourCity&ywsid=YourYelpAPI")),
        businesses = Source[businesses],
        businesses1 = Table.FromRecords(businesses),
        RemovedColumns = Table.RemoveColumns(businesses1,{"rating_img_url",  "id", "is_closed", "rating_img_url_small", "photo_url", "url", "city", "mobile_url", "nearby_url", "photo_url_small", "state_code", "categories", "neighborhoods", "country", "reviews", "zip", "state"}),
        ReorderedColumns = Table.ReorderColumns(RemovedColumns,{"name", "avg_rating", "review_count", "address1", "address2", "address3", "country_code", "phone", "distance"}),
        RemovedColumns1 = Table.RemoveColumns(ReorderedColumns,{"phone", "distance"})
    in
        RemovedColumns1

    Thursday, April 10, 2014 10:33 PM
  • Thanks everyone for the feedback and contributions to this thread.

    As Rodrigo, Ben and Zafar from the Power Query team already mentioned, this is a known issue and something that we're trying to improve in the future.

    I wanted to also provide the list of Data Model table operations that will cause connections to be marked as read-only, which then cause the errors after making additional changes to PQ queries and trying to refresh the corresponding Data Model table.

    • 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.

    Hope this helps.
    M.


    Friday, April 11, 2014 3:35 AM
    Owner
  • Thank you for the information Miguel.

    Was there any estimate provided by the Power Query team on when this issue may be resolved?

    This is a deal breaker for the project I'm currently working on.

    Thanks Again,

    Eric

    Thursday, April 17, 2014 4:02 PM
  • This is killing me too. I am using excel 2013 64 bit and every time I change anything in power query that is already in the data model, the error happens and I have no choice but unload/reload. I have learnt to back up often. FYI, I created a dummy table with 1 row, and then moved all my measures to that table. Then I fixed the power query and reloaded. At this stage the model grows error after error until you fix any issues in the measures. I then moved them back to the correct table. I am now only using power query for Unpivot. It is too unreliable for anything else.
    Tuesday, April 29, 2014 11:57 AM
  • Hi Dale,

    I'm using dataconnections to Excel files (Win8, Excel 13 64bit), created via PowerQuery. Your Workaround doesn't work for me. I'm not able to save the changed Connection saying I have to set the db Name. But this seems to be another bug, because I'm not able to save a file Connection changing nothing.

    My Workaround Looks like this:

    1. Load the Data into Excel

    2. Link the Excel Table into the PowerPivot model

    Now I'm able to Change the powerquery e.g. add another file as datasource via Table.Combine without any complaints.

    regards

    Daniel



    Regards
    Daniel Saager
    http://www.appliedtechnologies.de dsaager@appliedtechnologies.de
    please mark the post accordingly :-)

    Saturday, May 10, 2014 7:25 AM
  • Would a workaround to this be to handle all data manipulation, data cleaning, custom columns with formulas etc. in Power Query?
    • Proposed as answer by Jiminie Friday, August 01, 2014 7:24 PM
    Tuesday, May 20, 2014 11:06 PM
  • Is there any news on a fix for this?!!!! I have created a Power Pivot model based on several Power Query queries and I need to rename the columns in Power Pivot to be more user friendly. I can't do this because when I refresh the queries I need to disconnect them from the model and then reconnect them and it undoes all my work in Power Pivot.

    I need a fix for this ASAP!!!

    Tuesday, May 27, 2014 8:03 PM
  • I am finding that as long as the table that needs to be renamed is not specifically used in a calculation or relationship, then I can rename in Power Query and it does not blow up.  In one case, we just removed the relationship from the model (which caused several measures to go into error state), made the adjustment in Power Query, then put back the relationship to the renamed column.  The model and the measures adjusted fine.

    I was able to add and remove columns from Power Query as well - with the same caveat.


    Thanks, Barbara

    Tuesday, June 03, 2014 10:58 PM
  • Thank you all for your suggestions.

    We do have plans to have this fixed, it is not just a Power Query issue so we need to coordinate with different teams across the company. Which makes the timeframe more complicated.

    May I suggest (another workaround) to do all the column renaming and reordering upfront using Power Query? Once that is finished you can switch to Power Pivot, do all the calculations and you will still be able to refresh the data.

    Thanks,

    Rodrigo

    Tuesday, June 03, 2014 11:16 PM
  • Do you know when it is anticipated that this will be fixed? 
    Thursday, June 05, 2014 12:54 PM
  • Just to confirm, any custom columns with formulas that I added in Power Pivot, I would need to add those in Power Query too, correct?  

    Some of my added columns are really just intermediate formulas that I used to build measures.  In those cases, instead of adding columns in Power Pivot or Power Query, I should work those formulas into my measures, correct?  In turn, creating a leaner and faster Power Pivot experience.  

    Thanks for your continued responses to this issue.  

    Jim



    Friday, June 13, 2014 6:12 PM
  • In general, the earlier in the process that you can do things the better. So creating columns in Power Query is preferred over doing them in Power Pivot.
    Friday, June 13, 2014 6:55 PM
    Moderator
  • I'm also using Excel 2013 (like Daniel), so I also haven't had much success getting Dale's "reset the connection" workaround to work. Daniel's workaround of going through an Excel table seems less safe though, as I suppose it won't protect against adding new columns or the like.
    Has anyone been able to get something akin to Dale's workaround to work in Excel 2013?
    Monday, June 30, 2014 3:46 AM
  • Massage the data in PQ, then add measures in PP.  Problem solved.  
    • Proposed as answer by Jiminie Friday, August 01, 2014 7:24 PM
    Friday, August 01, 2014 7:23 PM
  • Is there a connect item for this that I can vote on/monitor?

    The "one way"/waterfall sldc style of this current setup doesn't work at all with BI/self-service BI. BI is iterative by nature, because the business changes over time, source systems change over time, requirements change over time.

    Needing to completely blow away the entire solution and rebuild it from scratch because you need to add a UPPER transformation to a column you're grabbing using power query = power query + power pivot is not a production ready tool.


    Jakub @ Adelaide, Australia Blog

    Thursday, December 04, 2014 6:31 AM
  • Bump.

    I agree with Jakub. When working with complex data structures in the data sources I usually need to redesign PQ several times and I always have a large set of calculations in PP, so redoing PP each time is very very irritating. Any news on when this is going to be fixed/improved?

    Thanks,

    Michal

    Monday, December 29, 2014 7:31 PM
  • Sometimes it is problem with referation integrity between tables. It could be duplicity in ID 1:n relationship.

    Jan

    Monday, February 23, 2015 8:16 AM
  • Until it's fixed here's something that might help.

    If you have to unload and reload your data to make it work, instead of rebuilding all your measures you can:

    - move your measures to another table of your data model temporary

    - unload your PQ data from PP

    - Reload it

    - move back your measure to the reloaded table.

    If someone has a tip to do something similar wit hadded columns I'd love to hear it!

    Wednesday, April 29, 2015 7:06 PM