Forum Discussion
POWERPIVOT Question - How does one Redirect the source of a PowerPivot to a new t able?
Simple question but have not found an answer. We have a SQL table, lets call it TblOld. It has 15M rows. We have a powerpivot conencted to it in RpeortOld.xlsb. Everything works great.
We have a copy of tblOld called tblNew. It has the same columns and rows. We are populating this sql table by a different means than tblOld. This is work in progress.
I opened and saved ReportOld as ReportNew.xlsb. Now I want to re-point the powerpivot to: tblNew. In other words I'd prefer not to rebuild the existing powerpivot but rather just repoint it to a new table.
I have tried a variety of things---no success.
Can someone outline how to do this?
All, I am back from a few days away. FYI The above post just lets me change data bases not tables.
I DID find a solution.
1) Be sure your IT dept does not downgrade you to an OFFICE 32 bit install and not tell you. (LOL)2) When they do , have them reinstall OFFICE 64 bit!
Now I can repoint the table in this manner without getting weird errors. Is there a better way, you tell me?Under Table properties, pick a new source name in the drop down. The drops shows all tables in the db...
That seems to now work. The data refreshes. Close the Window. The Manage table tab name retains the ORIGINAL table name, but the actual table used (just selected) can now be seen under: Edit Table Properties and now shows the new table and sure enought it refreshed correctly --I check the number of rows returned........AND...my pivots are maintained.
8 Replies
Hello - you shoul be able to just change the data source.
Follow these steps:
-
In the Power Pivot window, click Home > Connections > Existing Connections.
-
Select the current database connection and click Edit.
Edit the connection to select another database.Click Save > Close.
-
Click Home > Get External Data > Refresh > Refresh All.
The tables are refreshed using the new data source, but with the original data selections.
-
To point to a different table you can go into Power Query and just update the source table.
All, I am back from a few days away. FYI The above post just lets me change data bases not tables.
I DID find a solution.
1) Be sure your IT dept does not downgrade you to an OFFICE 32 bit install and not tell you. (LOL)2) When they do , have them reinstall OFFICE 64 bit!
Now I can repoint the table in this manner without getting weird errors. Is there a better way, you tell me?Under Table properties, pick a new source name in the drop down. The drops shows all tables in the db...
That seems to now work. The data refreshes. Close the Window. The Manage table tab name retains the ORIGINAL table name, but the actual table used (just selected) can now be seen under: Edit Table Properties and now shows the new table and sure enought it refreshed correctly --I check the number of rows returned........AND...my pivots are maintained.
