none
Out of line Object 'Data Source'... Please disable and re-enable the loading of the data model... RRS feed

  • Question

  • Hi Folks 

    I am pushing data from PowerQuery into an existing Powerpivot. If I make any changes to the PowerQuery, I get the error in the screenshot. With the changes I make, no additional fields are added, but I assume some metadata is being altered. 

    So I am forced to disable and re-enable themodel. But this wipes the dashboard that sits on top of the Powerpivot (as the model is deleted) . This is a major pain as the DAX in the powerpivot is also deleted with the model 

    I am running version Version: 2.13.3688.242 which appears to the most recent release of PowerQuery (25/6/2014).

    Any work arounds or fixes would be appreciated.
    Cheers

    Steve


    Friday, July 18, 2014 1:56 AM

Answers

  • Hi Steve,

    This is a known issue between Power Query and Power Pivot. Unfortunately, there isn't a straightforward workaround once you get into this situation.

    Are you using Excel 2013 or 2010? If you are using Excel 2010, you may be able to get out of that situation by following this workaround: http://www.powerpivotpro.com/2014/07/i-modified-an-existing-table-in-power-query-and-now-it-wont-refresh-a-fix/

    This won't work in Excel 2013 though. The root cause is that there are certain operations on top of Data Model tables that turn the underlying connection into "read-only", which prevents subsequent modifications to the query from Power Query to be saved into the connection.

    The below actions will create this "read-only" state in your connection. The "solution" is to make these 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.

    Every other action in Power Pivot (like Calculated Fields, KPIs, etc.) will work just fine, but you'll need to avoid the ones listed above to not get into this issue.

    Hope this helps.

    Thanks,
    M.

    Friday, July 18, 2014 4:33 PM

All replies

  • Hi Steve,

    This is a known issue between Power Query and Power Pivot. Unfortunately, there isn't a straightforward workaround once you get into this situation.

    Are you using Excel 2013 or 2010? If you are using Excel 2010, you may be able to get out of that situation by following this workaround: http://www.powerpivotpro.com/2014/07/i-modified-an-existing-table-in-power-query-and-now-it-wont-refresh-a-fix/

    This won't work in Excel 2013 though. The root cause is that there are certain operations on top of Data Model tables that turn the underlying connection into "read-only", which prevents subsequent modifications to the query from Power Query to be saved into the connection.

    The below actions will create this "read-only" state in your connection. The "solution" is to make these 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.

    Every other action in Power Pivot (like Calculated Fields, KPIs, etc.) will work just fine, but you'll need to avoid the ones listed above to not get into this issue.

    Hope this helps.

    Thanks,
    M.

    Friday, July 18, 2014 4:33 PM
  • Thanks for clarifying this.  

    Saturday, July 19, 2014 2:53 AM
  • Wow! This is a big problem. I thought I was using the MS BI Stack, not playing MS BI Jenga!
    Tuesday, October 21, 2014 3:35 PM
  • Yup - show stopper for us. Have switched to using a Tableau... 
    Tuesday, October 21, 2014 10:12 PM
  • I ran into a similar problem and tried duplicating the query and then ran the duplicated query. It was successful; thus I deleted the initial query and just kept the duplicated one. 
    Tuesday, October 21, 2014 11:30 PM
  • this worked fine. However, I deleted the worksheet that the query was loading to and then the query.
    Friday, September 1, 2017 5:09 PM