POWERPIVOT Question - How does one Redirect the source of a PowerPivot to a new t able?

[MrBobby]

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?

[MrBobby · Accepted solution]

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.

[MrBobby]

That is the issue. The screen you descibe ONLY lets you point to ANOTHER database. I want to point to ANOTHER TABLE in the SAME data base.

[jennratten]

To point to a different table you can go into Power Query and just update the source table.

[MrBobby]

I will poke around..I have never used PowerQuery.

[MrBobby]

Hmmm..where do I find that.  Right Now I just click PowerPivot then Manage. Isee this:

Once here, Existing connections allows me to EDIT the connection but ONLY point to a new database..not a new table in the existing data base.

If I got to the Design tab and click: Table Properties:

It looks like I can pick a new table...

BUT... After it brings the data up to preview...which is fast I only have 50 rows in the table at the moment...I get this bogus error:

I know there is no memeory issue.. the real table has 15M rows and refreshes without issue. I am trying to repoint to a table with 50 rows!

So apprently this is not where one should go to repoint a table...the thing is..I am pretty sure this is how I did it a year or more ago!...I can not be 100% sure..perhaps the software also changed.

What is the reccomended way to repoint?

[jennratten]

On the Excel ribbon, go to Data > Queries and Connections.  The Queries and Connections pane will appear with your queries and connections displayed.  You can right-click on them and then choose Edit.  That will open a new window with the Query Editor (aka Power Query).

[MrBobby]

My version of excel does not offer the Edit capability:

It does have properties but nothing to re-point a table:

[jennratten]

Hello - you shoul be able to just change the data source.  

Follow these steps:

1. In the Power Pivot window, click Home > Connections > Existing Connections.

2. Select the current database connection and click Edit.

   Edit the connection to select another database.Click Save > Close.

3. Click Home > Get External Data > Refresh > Refresh All.

   The tables are refreshed using the new data source, but with the original data selections.

   https://support.microsoft.com/en-us/office/make-changes-to-an-existing-data-source-in-power-pivot-9c...