Copy and paste rows into a Data Model in Power Pivot

If you're using a Data Model as the basis for a PivotTable, PivotChart or Power View report, and your model includes data that you copied from other sheets, you can add more rows by pasting them into the Power Pivot window.

The Paste Append and Paste Replace options are only available when you add or replace data in a table that was created by copying and pasting from the Clipboard. You cannot use Paste Append or Paste Replace to add data into a Linked Table or into a table of imported data.

The data that you paste from the Clipboard must be in HTML format, such as data that is copied from Excel or Word. You can also copy and paste a whole table or a range within Power Pivot.  Power Pivot will automatically detect and apply data types to the pasted data. However, you can also manually modify the data type or display formatting of a column. 

Note:  Use an alternative approach if you want to continuously update worksheet data and have those updates appear in the model. See Add spreadsheet data to a Data Model using linked tables for more information.

1. Copy the data to the Clipboard.

2. Open the Power Pivot window.

3. Click Home > Clipboard > Paste.

4. In the Paste Preview dialog box, provide a name for the table that Power Pivot creates to store the data.

5. Click OK to paste the data into a table. Notice that the table is part of the model, but is not a linked table. The table you just created is a standalone table that you manually maintain by pasting updates from the Clipboard.

6. Later, you can add or replace rows in this table. Open the tab containing the table for which you want to add rows and do one of the following:

   • Click Home > Clipboard > Paste Append to paste the contents of the Clipboard as additional rows into the selected table. The new rows will be added to the end of the table.

   • Click Home > Clipboard >Paste Replace to replace the selected table with the contents of the Clipboard. All existing column header names will remain in the table and relationships are preserved so you do not need to copy column headers from the original source.

     Notes: 

     • When you use Paste Append or Paste Replace, the new data must contain exactly the same number of columns as the old data. Preferably, the columns of data that you paste or append should also be of the same data types as those in the destination table, or be of a compatible data type. In some cases you can use a different data type, but usually a Type mismatch error will be displayed.

     • The Paste Append and Paste Replace options are only available when working with a table that was initially created by pasting data from the Clipboard. You cannot use Paste Append or Paste Replace to add data into a Linked Table or into a table of imported data.