none
"Change Data Source" feature disabled for Pivot Table on PowerPivot in Excel 2013

    Question

  • Hi,

    It seems like the "Change Data Source" feature of Pivot Tables is disabled in Excel 2013 when they are built on the Data Model (PowerPivot).

    To reproduce: just create a simple table in Excel, add it to the data model, build a pivot table on the data model, and try changing its data source: the option is disabled (greyed) in the ribbon.

    Anybody has a tip on how to resolve that?


    Friday, July 26, 2013 8:55 AM

Answers

  • What I did in one case is the following:

    1) Open the PowerPivot workbook

    2) Create a connection to the Tabular model and call it MyConnection

    3) Save the connection to Tabular into a file

    4) Create PivotTables based on connection MyConnection

    5) Change the connection created in 2) copying the information that have been created for ThisWorkbookDatamodel connection - now you are connected to PowerPivot data model through the MyConnection

    5) In order to go back to Tabular, change MyConnection and open the file you saved in 3)

    I am worried it is not supported.... but it worked to me


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    Friday, July 26, 2013 3:41 PM

All replies

  • Hi

    As the pivottable is a "Power Pivot" pivottable - it can only be linked to "ThisWorkbookDatamodel".

    So if you want to change the pivottable datasource you now have to modify the tables in the power pivot model and not via the Excel interface.

    And you can change the table via the tab "Linked tables" in the Power Pivot Window.

    BR

    Erik

    Friday, July 26, 2013 12:51 PM
  • Erik, thanks for your answer. Please let me precise our context.

    What we are doing here is a migration from PowerPivot 2013 to SSAS Tabular. If everything went really well on the "db" side (import in VS2012 and publish on SSAS), we really want to avoid having to redevelop every dashboards already done in Excel using PivotTables.

    Letting the PowerPivot Data Model between the Pivot Tables and SSAS is not a solution for us as one of the reasons we did migrate for was to decrease the size of the Excel files (90Mb at the moment, with only 2 years on 10 of historical data).

    Despite the adoption of the Data Model in Excel 2013, I'm really surprised that we can't change the data source of a "Power Pivot" pivottable to a Tabular instance. This is a really common scenario that was sold by MS when they introduced Tabular.

    Maybe someone from Microsoft could comment on that?

    Thanks a lot!

    Friday, July 26, 2013 1:24 PM
  • What I did in one case is the following:

    1) Open the PowerPivot workbook

    2) Create a connection to the Tabular model and call it MyConnection

    3) Save the connection to Tabular into a file

    4) Create PivotTables based on connection MyConnection

    5) Change the connection created in 2) copying the information that have been created for ThisWorkbookDatamodel connection - now you are connected to PowerPivot data model through the MyConnection

    5) In order to go back to Tabular, change MyConnection and open the file you saved in 3)

    I am worried it is not supported.... but it worked to me


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    Friday, July 26, 2013 3:41 PM
  • Indeed it seems like there is no supported method to do that at the moment.

    Thanks for your tip Marco, but I'll just redevelop everything.

    Wednesday, July 31, 2013 2:24 PM