Power Pivot Principles: Importing Data

Overview

Power Pivot has the ability to import data from a variety of sources: e.g. a database, data service, Excel worksheet and even data feeds, just to name a few. Once the data has been imported, you can make changes to your data or add new data to the source data which will refresh the Power Pivot data model. We will go through some of the different methods to import data below.

Link from Excel

Data that has been loaded into Power Pivot is unable to be modified. That’s a good thing. To circumvent this issue, load data from Excel. Loading data from a table in Excel enables you to make changes to the data in Excel, e.g. adding rows or columns, which in turn will cascade through to the Power Pivot file when the data is refreshed. (Linking data is no longer possible as it was discovered this was causing file corruption issues.)

Copy and Paste

Another way to bring data into Power Pivot is by copying and pasting data. However, there are several limitations to loading data this way. For example, the columns become fixed once the data is pasted. It is not possible to add extra columns of data but you can create new calculated columns.

It is also possible to ‘Paste Append’ and ‘Paste Replace’. If these methods are used the data source must be exactly the same as the Power Pivot file. Please be mindful of this limitation before copying and pasting data.

Database

To bring in data from a database, you will have to use the Table Import Wizard. With this wizard, you are able to import data from a variety of relational databases such as SQL Server and Access Database. For more information on this please visit: Import data from a database.

Data Format

It is important to note that once you have imported your data into PowerPivot, the source type cannot be changed. If you create a table via an Excel file you cannot change it in later to be linked from a database or another source. The table will need to be deleted and then re-created from the new source.

Editing Data

Once data is loaded into Power Pivot, it is unable to be changed from the Power Pivot window. Power Pivot data is read-only, so any changes that are required will need to be made from the source data (If possible) and then refreshed.

Now that we have a bit of an overview of how data can be imported in the next blog we can look at some examples of importing data next time. Stay tuned for our next post on Power Pivot. In the meantime, please remember we have training in Power Pivot which you can find out more about here.