locked
Excel Command Text won't update after updating the PowerPivot Table Definition RRS feed

  • Question


  • When i create a connection in powerpivot everything work fine. I can close the file reopen it and refresh the connections without issues. However, if want to modify the table properties of the connection (like adding a column), the command text in excel doesn't update (and it's grayed out so I can't manually update it either) . which creates a problem when I close the excel file and reopen it. Because now if I try to refresh the file it fails. One I create a connection in powerpivot and I need to modify it, I have to delete the whole connection and re-add it. which is a lot of pain since there's so many things I need to change if I do that and it wastes a lot of time.

    Command text box

    Command Text

    PowePivot table properties update

    Wednesday, October 22, 2014 8:28 PM

Answers

  • bellicose,

    Let's see if I understood this correctly

    1. You create a connection and pull data into Power Pivot (this actually creates two connections - One in Excel and one in PowerPivot)

    2. You make changes to the Power Pivot connection e.g. to pull in additional table

    3. This renders the Excel connection read only (this is the expected behavior)

    4. Your data refresh breaks - you are unable to refresh data?! This is unexpected :-(

    In step 1 - are you pulling data directly into Power Pivot? i.e. are you doing it from the Power Pivot window?
    I tried the steps as above and did not have any trouble refreshing in step 4.


    Regards, Avi www.powerpivotpro.com

    Thursday, October 23, 2014 2:36 AM

All replies

  • bellicose,

    Let's see if I understood this correctly

    1. You create a connection and pull data into Power Pivot (this actually creates two connections - One in Excel and one in PowerPivot)

    2. You make changes to the Power Pivot connection e.g. to pull in additional table

    3. This renders the Excel connection read only (this is the expected behavior)

    4. Your data refresh breaks - you are unable to refresh data?! This is unexpected :-(

    In step 1 - are you pulling data directly into Power Pivot? i.e. are you doing it from the Power Pivot window?
    I tried the steps as above and did not have any trouble refreshing in step 4.


    Regards, Avi www.powerpivotpro.com

    Thursday, October 23, 2014 2:36 AM
  • Here's what I'm doing,

    (Please note this doesn't happen when I connect to SQL Server, only to ODBC connections to SQLBase Server in PowerPivot)

    From the Powerpivot window -> connect to data source -> others (OLEDB/ODBC)
    then, build -> select the data source -> then enter username & password (I do not click "Allow saving password here" as it doesn't work, it for whatever reason doesn't save password here and would ask me for it the next time I refresh)

    then on the next windows I write the SQL Query to pull the data, the data is loaded in the powerpivot window,

    then I go to back to excel window -> workbook connections - > properties -> and under Definition select "Save Password"

    then I go back to powerpivot wondow -> refresh the connection, this time it will ask for password but would save it permanently to the excel book.

    at this point, the excel works fine and I can refresh it without any issues.

    now, if i want to modify the sql query to say add anther column, I go the the table properties and update the query there, then when I save it it will update the data in the powerpivot window. If I save & close the excel at this point and reopen it, the powerpivot won't refresh

    If i go to the excel connection properties I still see the old query under command text but in the powerpivot window table properties it shows the new query, I am assuming that's where the problem lies,

    if there's a way to update the command text when the table property is updated that would probably resolve the issue i'm assuming, however, it's grayed out.

    Thursday, October 23, 2014 6:24 PM
  • Bellicose, is this still an issue?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Saturday, December 13, 2014 12:15 AM
  • Yes, this issue is present in every single workbook I have that has ODBC connection created in PowerPivot
    Friday, January 2, 2015 8:00 PM
  • Yes, this issue is present in every single workbook I have that has ODBC connection created in PowerPivot

    Did you try following Michael's steps from scratch (new file)?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, February 4, 2015 7:36 PM
  • Who's Michael? and where are the steps. I must have at least 100 files that have this issue (all of them created from scratch)
    Saturday, February 7, 2015 3:18 AM