locked
How to move models and changes in models between offline Excel PowerPivot files? RRS feed

  • Question

  • I have created an excel report, a pivot table based on a Power Pivot model. The purpose is to use this report offline. The plan is to build other reports, separate workbooks, based on the same model. Now my concerns are maintaining the model, what if I need to add something, or change something and I want the model to be consistent between my reports. How do I move my change/addition from one file to another the most efficient way?

    Does anyone know good practice for moving the model between reports?

    I found one way (copying worksheet to other workbook) but that added a new model with suffix 1 after the name, so the excel report I copied the worksheet to got two power pivot models, e.g. Model and Model1.

    Best regards,

    Sandra
    Friday, October 21, 2016 6:56 AM

Answers

  • Friday, October 21, 2016 8:21 AM
  • Hi FSandraF,

    As other post, SSAS Tabular is an better way to importing the metadata and data from a Power Pivot workbook.

    An Excel workbook can contain only one Data Model, we only can copy the model by completing Excel workbook to get a 1:1 copy.

    1) rename your .xlsx to .zip.
    2) check for \xl\model an you will find a file called item.data which is basically your PowerPivot model.
    3) copy this file to the same location in of an other workbook. There are also some other files necessary so I would recommend to create a new workbook with a very simple Power Pivot model (e.g. 1 single table) and replace the file there.

    In addition, you can copy a pivot table from one workbook to another, and the underlying model and connections will get copied as well. This thread gives the detailed steps. For more details about PowerPIvot model, please refer to this article.

    If you have any question, please feel free to ask.

    Best Regards,
    Angelia


    Monday, October 24, 2016 10:16 AM
  • This is definitely an issue. You could ingrate you master model to power Bi desktop and then load it to the Power BI service. Once it is there, you can create Excel workbooks using "Analyse in Excel". These new thin workbooks will retain a connection to the master workbook, and all you need to do is maintain 1 Modell.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Monday, October 24, 2016 10:45 AM
    Answerer
  • Hi FSandraf,

    I test it in Excel 2016 version. I create a pivot table report in one excel, I copy the report to another new excel, it work fine, and the table model is also moved. For another, I select one model in Power Pivot Window and click Paste highlighted in yellow like below screenshot, then the model is moved successfully.



    How do you copy the data model file from new zipped report to the old zipped report and it was not possible to paste the into the zipped folder? Please refer to my steps and check if it is successful, thanks a lot.

    Best Regards,
    Angelia
    Thursday, November 3, 2016 2:00 AM

All replies

  • Friday, October 21, 2016 8:21 AM
  • Hi FSandraF,

    As other post, SSAS Tabular is an better way to importing the metadata and data from a Power Pivot workbook.

    An Excel workbook can contain only one Data Model, we only can copy the model by completing Excel workbook to get a 1:1 copy.

    1) rename your .xlsx to .zip.
    2) check for \xl\model an you will find a file called item.data which is basically your PowerPivot model.
    3) copy this file to the same location in of an other workbook. There are also some other files necessary so I would recommend to create a new workbook with a very simple Power Pivot model (e.g. 1 single table) and replace the file there.

    In addition, you can copy a pivot table from one workbook to another, and the underlying model and connections will get copied as well. This thread gives the detailed steps. For more details about PowerPIvot model, please refer to this article.

    If you have any question, please feel free to ask.

    Best Regards,
    Angelia


    Monday, October 24, 2016 10:16 AM
  • This is definitely an issue. You could ingrate you master model to power Bi desktop and then load it to the Power BI service. Once it is there, you can create Excel workbooks using "Analyse in Excel". These new thin workbooks will retain a connection to the master workbook, and all you need to do is maintain 1 Modell.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Monday, October 24, 2016 10:45 AM
    Answerer
  • Hi Angelia,

    I have tried to copy a workbook to another and followed the thread you mentioned. I do get a copy of the model/table with the suffix 1 just as mentioned in the thread. However, I don't manage to redirect my report/pivot table to use the new Model1 instead of old Model. Do you know?

    Thanks,

    Sandra

    Wednesday, November 2, 2016 3:48 PM
  • Hi again Angelia,

    I also tried to copy the data model file from new zipped report to the old zipped report and it was not possible to paste the into the zipped folder. Should that be possible? Have I missed any step to get this working?

    Kind regrds

    Sandra

    Wednesday, November 2, 2016 3:58 PM
  • Hi FSandraf,

    I test it in Excel 2016 version. I create a pivot table report in one excel, I copy the report to another new excel, it work fine, and the table model is also moved. For another, I select one model in Power Pivot Window and click Paste highlighted in yellow like below screenshot, then the model is moved successfully.



    How do you copy the data model file from new zipped report to the old zipped report and it was not possible to paste the into the zipped folder? Please refer to my steps and check if it is successful, thanks a lot.

    Best Regards,
    Angelia
    Thursday, November 3, 2016 2:00 AM
  • Hi,

    I have tested both your suggestions and have not been successful.

    - I copy the item.data file and when I want to paste it into PowerPivot the Paste option is greyed out/disabled.

    - My model consist of only one table called Data. I added a new field to my model in PowerQuery one excel document (SalesRegionA.xlsx). I have several excel documunets, with the same model (copy), only filter on different sales region. To copy the updated model to my other workbooks I created a new worksheet with a pivot table using this new field. I copied this worksheet to another workbook (SalesRegionB.xlsx). By doing this I got a new table named "Data1". My pivot table in SalesRegionB is still using "Data" table and I do not know how to redirect it to use Data1 instead. Do you have any ideas?

    Thanks,

    Sandra

    Monday, November 28, 2016 2:08 PM