"Change Data Source" feature disabled for Pivot Table on PowerPivot in Excel 2013
Question
Friday, July 26, 2013 8:55 AM
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?
All replies (6)
Friday, July 26, 2013 3:41 PM âś…Answered | 2 votes
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 12:51 PM
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 1:24 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!
Wednesday, July 31, 2013 2:24 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.
Friday, May 8, 2015 2:50 PM
not sure how exactly technically to do the steps:
3) does it mean just saving odc connection file?
1-2-3-4 steps - so far so good - done
not sure how to perform step 5 and 6
help appreciated. I have a client who has PowerPivot model and has done some reports on it.
Now When we migrate the nicely tested model to Tabular server, I want him to be able to repoint his reports to the server model, well if it is possible.
My understanding from this Answer is that it would have been possible, have I done steps 1-5 with him before, so he would originally have created his reports from that specially prepared connection.
Am I right?
Wednesday, March 27, 2019 1:42 PM
Hi
I am trying to repoint from external sql connection to local sheet/table connection and the option in Excel 2013 and 360 is disabled.
My excel file has 10 pivot tables and dependent charts from -pointing to- an external sql data connection.
Current steps taken:
1. I have created an extra sheet/table and created a new test pivot table on the basis of the sheet/table and added it to the data model.
2. I have also created an ocd file for both the sql connection and the datamodel.
3. As an attempt to hack the connection I have pointed the sql connection to the datamodel ocd and excel complains of illegal/corruption file with a message.
So I am at a loss at the moment,
Please help! :)
Thanks