locked
How to change external table source for an existing PivotTable? RRS feed

  • Question

  • Hello,

    I have a PivotTable formatted exactly as I'd like, and would now like to copy it and have it pointing to a new PowerPivot table listed in the PivotTable Field List box.  The new table has the exact same fields as the original, only the SQL query criteria has changed.  My frustration is that I'm finding no way to change the data source to the new table.

    Any assistance provided here will be most appreciated!

    Thursday, August 4, 2016 6:22 PM

Answers

  • Hi,

    Did you want to change fron External Data to Table?

    As far as I know you cannot change from External Data to Table, if you create PivotTable from External Data you cannot change the data source from External Data.

    In my opinion, you need to create a new PivotTable.

    If my understanding is incorrect please provide more information about your problem.

    I will provide more hlep about your problem, I'm glad to help you.


    Regards

    Emi Zhang

    TechNet Community Support

    Please mark the reply as an answer if they help and unmark them if they provide no help.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.


    Sunday, August 7, 2016 11:51 AM

All replies

  • Hi,

    Did you want to change fron External Data to Table?

    As far as I know you cannot change from External Data to Table, if you create PivotTable from External Data you cannot change the data source from External Data.

    In my opinion, you need to create a new PivotTable.

    If my understanding is incorrect please provide more information about your problem.

    I will provide more hlep about your problem, I'm glad to help you.


    Regards

    Emi Zhang

    TechNet Community Support

    Please mark the reply as an answer if they help and unmark them if they provide no help.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.


    Sunday, August 7, 2016 11:51 AM
  • I have a PivotTable formatted exactly as I'd like, and would now like to copy it and have it pointing to a new PowerPivot table listed in the PivotTable Field List box.  The new table has the exact same fields as the original, only the SQL query criteria has changed.  My frustration is that I'm finding no way to change the data source to the new table.

    Any assistance provided here will be most appreciated!

    Monday, August 8, 2016 6:15 PM
  • Hi Emi,

    My goal is to copy a PivotTable, and have it point to a new, external PowerPivot table that's simply based on different SQL criteria.  Nothing would change with the table's field structure.  While it's not looking easily doable, I thought that just maybe somewhere in the background there’d be a way of editing the PivotTable’s table name via metadata properties, negating the need to start over and create a new PivotTable.  Perhaps I'm being overly optimistic.

    Tuesday, August 9, 2016 12:10 PM
  • Try this,

    (maybe the labels would differ somehow because I have an Excel italian version).

    1. Open Properties under Connection of Data menu bar;

    2. Click "Modify query" under Definition tab;

    3. Cancel (it will open the dialog to modify the query in Microsoft Query, Accept);

    4. Click SQL button and modify the query;

    5. Click OK and exit Microsoft Query;

    6. Click Ok on the Querry property tab.

    Hope will help.

    Best regards.

    Tuesday, August 9, 2016 12:53 PM
  • Thanks Rocco, but I've already created the table in the PowerPivot window, and it's showing in Excel's PivotTable Field List window.  The problem is I can't change a copy of the PivotTable so that it's pointing to the new table without losing all of the PivotTable's formatting.
    Tuesday, August 9, 2016 2:08 PM
  • Maybe I'm wrong, I didn't say to create a new source, I said to modify the original query source.

    Best regards.

    Tuesday, August 9, 2016 2:23 PM
  • The original PivotTable is still used and needs its query source kept as is.
    Tuesday, August 9, 2016 2:42 PM
  • Create a copy of the Excel Workbook file.

    Copy or move the worksheet from the new file to the old file version. Excel will create a new source along with the worksheet you copied.

    Modify the source of the new worksheet.

    Tuesday, August 9, 2016 2:49 PM
  • When I made a copy of the worksheet, it retained the same source as the original.
    Tuesday, August 9, 2016 3:08 PM
  • I tried with my Excel 2016 before answer.

    In my test I created a different new data source (identical to original) that I can modify indipendently.

    Tuesday, August 9, 2016 3:15 PM
  • I'm using Excel 2010, not sure if that matters.  I too created a different new data source (identical to original as to fields), the issue I'm having is trying to point the new (copied) PivotTable to the new external data source.  Is there a way of doing that in 2016?
    Tuesday, August 9, 2016 5:52 PM
  • I'll check as soon as I get at work.

    Regards.
    Wednesday, August 10, 2016 5:35 AM
  • Hi, sorry for the late answer.

    I tried the operation with Excel 2010 and don't have any problem.

    For sake of clarity let me summarize.

    You have an Excel workbook. In this workbook there is a worksheet with a Pivot table. The Pivot table has a source query like "SELECT COLA, COLB, COLC FROM QURY1" (example).

    You would like to create a new worksheet with the same Pivot (formats, etc) and a different query like "SELECT COLA, COLB, COLC FROM QURY2".

    The steps I suggested were:

    1. In Explorer, right click the workbook file and click copy;

    2. Right click an empty space in the folder and click paste;

    3. Open both old and new workbook;

    4. In the copy workbook, right click the name of the worksheet and click "Move or copy";

    5. In the dialog "Move or copy" select the old workbook (Folder combobox), check move and click OK. The worksheet is moved to the old workbook;

    6. Open Connections under Connection group of Data menu;

    7. Verify that there are two identical data source;

    8. Select the new data source and click Property

    9. Click "Modify query" under Definition tab;

    10. Cancel the "Creation query wizard" (it will open the dialog to modify the query in Microsoft Query, Accept);

    11. Click SQL button and modify the query;

    12. Click OK and exit Microsoft Query;

    13. Click Ok on the Query property tab.

    I'll wait for update.

    Best regards.


    Wednesday, August 10, 2016 11:50 AM
  • Rocco,

    All went fine until #7, because the connection used by the worksheets is called PowerPivot Data, and has this description: "This connection is used by Excel for communication between the workbook and embedded PowerPivot data, and should not be manually edited or deleted."  Selecting Properties for PowerPivot Data doesn't offer a 'Modify query' option under the Definition tab (it shows the "Connection type" as OLAP Query).  It appears the old and new data sources are somehow embedded in this connection, and that this connection type is different that the one your approach uses.  It was in the PowerPivot window where the data source queries were defined via the Table Properties button. 

    Since starting this thread, I went ahead and took Emi's suggestion: "In my opinion, you need to create a new PivotTable".  This approach of course required formatting the new PivotTable (a pain), but it seemed the surest way to get the job done.  I would, however, like to thank you for your time!

    Wednesday, August 10, 2016 3:01 PM
  • OK,

    on the base of your posts, I felt that there was something I missed.

    Sorry for the wasted time.

    Best regards.
    Thursday, August 11, 2016 5:37 AM