none
Copy from PowerPivot to Different Workbook

    Question

  • Simple question.  I have two sophisticated PowerPivot workbooks.  I just spent the last couple of hours adding a new data feed into one of the workbooks, complete with many customizations to the table in my data model.

    How do I copy the work that I've done on this table?  I want it in my other PowerPivot workbook so I can use the data feed from that one too.

    I am working with Excel 2010 and the PowerPivot plugin.  I don't have SharePoint.  I see that I can simply copy raw data out of one PowerPivot window into another but it's not bringing along my data feed connection or customizations.

    Any help would be appreciated.  I'm having a trouble finding this copy/paste feature...


    Friday, September 20, 2013 1:33 PM

Answers

  • David -

    Here's an approach that I know works in Excel2013, and I believe works in Excel2010 with the 2012 PowerPivot add-in.  You can copy a pivot table from one workbook to another, and the underlying model and connections will get copied as well. 

    0. Be sure to make a copy (backup) of both workbooks before following the steps below. 

    1. Open both workbooks: the one with the updated model (I'll call this New) and the other one that needs a copy of the new table / connection (I'll call this Old).

    2. In the "New" workbook, create a pivot table on a new worksheet based on your updated model.  In this pivot table, include only fields and measures from the updated table that you want to copy to the other workbook (it can be a simple pivot, just needs to be based on data from the table you want to copy over).

    3. Copy the worksheet with the new pivot on it from "New" workbook to "Old" workbook

       - right click the tab with the new pivot table and select "Move or Copy"; then select your "Old" workbook to move it to

    4. Clean up your "Old" model. Step three will copy not only the pivot table, but also the part of the underlying Power Pivot model that the "New" pivot table is based on.  This copy operation also copies the required connections to the "Old" workbook / model. 

    Note: if you have naming collisions between the two workbooks / models, the copy operation will append a "1" to the added table name and connection and may have problems recreating some calculated columns and measures.  But with the new table / connection added to the Old workbook, you should be able to hook your Old model up to the new table and delete the old table and move forward.

    Also note, this is not as elegant or bulletproof as the model merging that can be done with Analysis Services Tabular models with BISM Normalizer.  But my experience is that it works pretty well and has helped me save work on several prototypes where I needed to copy structures from one workbook to another.  While it can potentially make duplicate connections or other objects in the target workbook, if manually recreating the new / modified objects in your target workbook would be too time-consuming (or you are just doing proof-of-concept), this approach is worth a try.  

    Let me know if this helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Sunday, September 29, 2013 10:22 PM
    Answerer

All replies

  • Given SharePoint is not in the picture, can you possibly allocate your customizations outside of the PowerPivot Window and designate a separate source for it?  In that case, you can have two PowerPivot models (one for each workbook) consuming the one customized source.

    Would that help?




    Javier Guillen
    http://javierguillen.wordpress.com/

    Wednesday, September 25, 2013 1:53 PM
    Answerer
  • Given that I have two PowerPivot models, I'm hoping I can copy a portion of one of them (ie a single table's structure) into the other.

    The question is specifically about PowerPivot and I was looking for a way to do copy/paste within that new part of Excel. (I can't find it in the UI but maybe its in an API somewhere - ie export/import?)

    Is it possible that copy/pasting from one model to another was omitted deliberately?

     


    David Beavon

    Wednesday, September 25, 2013 7:13 PM
  • You can copy and paste from a PowerPivot window to another PowerPivot window (2 different workbooks).   Simply select the table in question, click control+c and then go to the other window and click 'Paste' on the menu.

    Keep in mind this copy is not updatable, only a one time dumping of data into the model and hence is not refreshable.   




    Javier Guillen
    http://javierguillen.wordpress.com/

    Friday, September 27, 2013 7:04 PM
    Answerer
  • David -

    Here's an approach that I know works in Excel2013, and I believe works in Excel2010 with the 2012 PowerPivot add-in.  You can copy a pivot table from one workbook to another, and the underlying model and connections will get copied as well. 

    0. Be sure to make a copy (backup) of both workbooks before following the steps below. 

    1. Open both workbooks: the one with the updated model (I'll call this New) and the other one that needs a copy of the new table / connection (I'll call this Old).

    2. In the "New" workbook, create a pivot table on a new worksheet based on your updated model.  In this pivot table, include only fields and measures from the updated table that you want to copy to the other workbook (it can be a simple pivot, just needs to be based on data from the table you want to copy over).

    3. Copy the worksheet with the new pivot on it from "New" workbook to "Old" workbook

       - right click the tab with the new pivot table and select "Move or Copy"; then select your "Old" workbook to move it to

    4. Clean up your "Old" model. Step three will copy not only the pivot table, but also the part of the underlying Power Pivot model that the "New" pivot table is based on.  This copy operation also copies the required connections to the "Old" workbook / model. 

    Note: if you have naming collisions between the two workbooks / models, the copy operation will append a "1" to the added table name and connection and may have problems recreating some calculated columns and measures.  But with the new table / connection added to the Old workbook, you should be able to hook your Old model up to the new table and delete the old table and move forward.

    Also note, this is not as elegant or bulletproof as the model merging that can be done with Analysis Services Tabular models with BISM Normalizer.  But my experience is that it works pretty well and has helped me save work on several prototypes where I needed to copy structures from one workbook to another.  While it can potentially make duplicate connections or other objects in the target workbook, if manually recreating the new / modified objects in your target workbook would be too time-consuming (or you are just doing proof-of-concept), this approach is worth a try.  

    Let me know if this helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Sunday, September 29, 2013 10:22 PM
    Answerer
  • Thanks Brent.  I think I'll have to upgrade to Excel 2013.  This doesn't work great in Excel 2010 (only works if the target workbook doesn't have a Model and never had one either).

    It seems like putting the cart before the horse to copy a pivot table prior to the Model, but whatever works.

    The error in Excel 2010 is :

    • A PivotTable on the range you are copying is connected to an embedded data source and Worksheet2.xlsx already has embedded data with the same identifier.  Excel will connect the pasted PivotTable to the existing data in Worksheet2.xlsx.  If the data is different, the pasted PivotTable may not refresh properly.

    This happens no matter what I name the tables within the PowerPivot model.   I think the issue is in the connection properties of the pivot table object itself.  The "Command Text" of both the source and target is simply "Model" and there doesn't appear to be an obvious way to change it.  No "model merging" seems to be performed in Excel 2010.

    I appreciate the feedback.  Will try and get my hands on Excel 2013 to check out "model merging".


    David Beavon

    Monday, September 30, 2013 7:55 PM
  • Thanks Brent.  I think I'll have to upgrade to Excel 2013.  This doesn't work great in Excel 2010 (only works if the target workbook doesn't have a Model and never had one either).

    It seems like putting the cart before the horse to copy a pivot table prior to the Model, but whatever works.

    The error in Excel 2010 is :

    • A PivotTable on the range you are copying is connected to an embedded data source and Worksheet2.xlsx already has embedded data with the same identifier.  Excel will connect the pasted PivotTable to the existing data in Worksheet2.xlsx.  If the data is different, the pasted PivotTable may not refresh properly.

    This happens no matter what I name the tables within the PowerPivot model.   I think the issue is in the connection properties of the pivot table object itself.  The "Command Text" of both the source and target is simply "Model" and there doesn't appear to be an obvious way to change it.  No "model merging" seems to be performed in Excel 2010.

    I appreciate the feedback.  Will try and get my hands on Excel 2013 to check out "model merging".


    David Beavon

    Does the upgrade to Excel 2013 help? I also have the Problem with this error message in Excel 2010. I have not found a solution for this Problem yet. Does the upgrade to Excel 2013 resolve this problem?

    Cheers

    Fabian Rücker

    Wednesday, April 09, 2014 12:47 PM