Microsoft Office Tutorials and References
In Depth Information
A better way to accomplish this task is to create an Excel worksheet that contains only the new
products and then merge it with the table that contains old data. In this way, the original table
can be updated by simply reloading it from the data source, and the only part of the process
that needs to be redone in case of update is the merging procedure.
So now we need to create a worksheet that can be appended to our table. The easiest way
to perform this task is to copy the original table from PowerPivot, paste it into a new Excel
worksheet, and then delete all the rows, retaining only the structure. When you do this, you
must be sure that column names are exactly the same and in the same order as the original
PowerPivot table. To perform this task, it is enough to open the PowerPivot window, select
the whole table, and do a copy and paste operation in Excel.
The resulting table, already filled with new data, is shown in Figure 8-27.
FIguRE 8-27 The Excel table is ready to be appended to the original PowerPivot table.
Now you could be tempted to copy and paste it directly to the original table. Unfortunately,
that does not work because the original table is tied to an external data source, and you
cannot add any rows to it. If you could to that, PowerPivot would not be able to refresh it
later. To merge the two tables, you need to create a new one and copy data from both the
prediction table and the new products table within it.
To create that table, you can simply copy the whole original table and then paste it into a new
table, which you call MergedSales. As soon as you click the Paste button, the Paste Preview
dialog box appears (see Figure 8-28).