Excel Power Pivot - Loading Data
In this chapter, we will learn to load data into Power Pivot.
You can load data into Power Pivot in two ways −
Load data into Excel and add it to the Data Model
Load data into PowerPivot directly, populating the Data Model, which is the PowerPivot database.
If you want the data for Power Pivot, do it the second way, without Excel even knowing about it. This is because you will be loading the data only once, in highly compressed format. To understand the magnitude of difference, suppose you load data into Excel by first adding it to the Data Model, the file size is say 10 MB.
If you load data into PowerPivot, and hence into Data Model skipping the extra step of Excel, your file size could be as less as 1 MB only.
Data Sources Supported by Power Pivot
You can either import data into the Power Pivot Data Model from various data sources or establish connections and/or use the existing connections. Power Pivot supports the following data sources −
SQL Server relational database
Microsoft Access database
SQL Server Analysis Services
SQL Server Reporting Services (SQL 2008 R2)
ATOM data feeds
Text files
Microsoft SQL Azure
Oracle
Teradata
Sybase
Informix
IBM DB2
Object Linking and Embedding Database/Open Database Connectivity
- (OLEDB/ODBC) sources
Microsoft Excel File
Text File
Loading Data Directly into PowerPivot
To load data directly into Power Pivot, perform the following −
The PowerPivot window opens. Now you have two windows − the Excel workbook window and the PowerPivot for Excel window that is connected to your workbook.
The Table Import Wizard appears.
Browse to the Access database file.
Provide Friendly connection name.
If the database is password protected, fill in those details also.
Click the Next → button. The Table Import Wizard displays the options for choosing how to import data.
Click Select from a list of tables and views to choose the data to import.
Click the Next → button. The Table Import Wizard displays the tables and views in the Access database that you have selected.
Check the box Medals.
As you can observe, you can select the tables by checking the boxes, preview and filter the tables before adding to Pivot Table and/or select the related tables.
Click the Preview & Filter button.
As you can see, you can select specific columns by checking the boxes in the column labels, filter the columns by clicking the dropdown arrow in the column label to select the values to be included.
Click OK.
Click the Select Related Tables button.
Power Pivot checks what other tables are related to the selected Medals table, if a relation exists.
You can see that Power Pivot found that the table Disciplines are related to the table Medals and selected it. Click Finish.
Table Import Wizard displays − Importing and shows the status of the import. This will take a few minutes and you can stop the import by clicking the Stop Import button.
Once the data is imported, the Table Import Wizard displays – Success and shows the results of the import as shown in the screenshot below. Click Close.
Power Pivot displays the two imported tables in two tabs.
You can scroll through the records (rows of the table) using the Record arrows below the tabs.
Table Import Wizard
In the previous section, you have learnt how to import data from Access through the Table Import Wizard.
Note that the Table Import Wizard options change as per the data source that is selected to connect to. You might want to know what data sources you can choose from.
Click From Other Sources in the Power Pivot window.
The Table Import Wizard – Connect to a Data Source appears. You can either create a connection to a data source or you can use one that already exists.
You can scroll through the list of connections in the Import Table Wizard to know the compatible data connections to Power Pivot.
Click the Next → button. The Table Import Wizard displays – Connect to a Microsoft Excel File.
Browse to the Excel file in the Excel File Path box.
Check the box – Use first row as column headers.
You will see the following Success message. Click Close.
You have imported one table, and you have also, created a connection to the Excel file that contains several other tables.
Opening Existing Connections
Once you have established a connection to a data source, you can open it later.
Click Existing Connections in the PowerPivot window.
The Existing Connections dialog box appears. Select Excel Sales Data from the list.
Click the Open button. The Table Import Wizard appears displaying the tables and views.
Select the tables that you want to import and click Finish.
The selected five tables will be imported. Click Close.
You can see that the five tables are added to the Power Pivot, each in a new tab.
Creating Linked Tables
Linked tables are a live link between the table in Excel and the table in the Data Model. Updates to the table in Excel automatically update the data in the data table in the model.
You can link the Excel table into Power Pivot in a few steps as follows −
Create an Excel table with the data.
Click the POWERPIVOT tab on the Ribbon.
Click Add to Data Model in the Tables group.
The Excel table is linked to the corresponding Data Table in PowerPivot.
You can see that the Table Tools with the tab - Linked Table is added to the Power Pivot window. If you click Go to Excel Table, you will switch to the Excel worksheet. If you click Manage, you will switch back to the linked table in the Power Pivot window.
You can update the linked table either automatically or manually.
Note that you can link an Excel table only if it is present in the workbook with the Power Pivot. If you have Excel tables in a separate workbook, then you have to load them as explained in the next section.
Loading from Excel Files
If you want to load the data from Excel workbooks, keep the following in mind −
Power Pivot considers the other Excel workbook as a database and only worksheets are imported.
Power Pivot loads each worksheet as a table.
Power Pivot cannot recognize single tables. Hence, Power Pivot cannot recognize if there are multiple tables on a worksheet.
Power Pivot cannot recognize any additional information other than the table on a worksheet.
Hence, keep each table in a separate worksheet.
Once your data in the workbook is ready, you can import the data as follows −
The following are the differences between linked Excel tables and imported Excel tables −
Linked tables need to be in the same Excel workbook in which the Power Pivot database is stored. If the data already exists in other Excel workbooks, there is no point in using this feature.
The Excel import feature allows you to load data from different Excel workbooks.
Loading data from an Excel workbook does not create a link between the two files. Power Pivot creates only a copy of the data, while importing.
When the original Excel file is updated, data in the Power Pivot will not be refreshed. You need to either set the update mode to automatic or update the data manually, in the Linked Table tab of the Power Pivot window.
Loading from Text Files
One of the popular data representation styles is with the format known as comma separated values (csv). Each data row /record is represented by a text line, wherein the columns /fields are separated by commas. Many databases provide the option of saving to a csv format file.
If you want to load a csv file into Power Pivot, you have to use the Text File option. Suppose you have the following text file with csv format −
Click the PowerPivot tab.
Click the Home tab in the PowerPivot window.
Click From Other Sources in the Get External Data group. The Table Import Wizard appears.
Scroll down to Text Files.
Click Text File.
Click the Next → button. Table Import Wizard appears with the display − Connect to Flat File.
Browse to the text file in the File Path box. The csv files usually have the first line representing column headers.
Check the box Use first row as column headers, if the first line has headers.
In the Column Separator box, default is Comma (,), but in case your text file has any other operator such as Tab, Semicolon, Space, Colon or Vertical Bar, then choose that operator.
As you can observe, there is a preview of your data table. Click Finish.
Power Pivot creates the data table in the Data Model.
Loading from the Clipboard
Suppose, you have data in an application that is not recognized by Power Pivot as a data source. To load this data into Power Pivot, you have two options −
Copy the data to an Excel file and use the Excel file as data source for Power Pivot.
Copy the data, so that it will be on the clipboard, and paste it into Power Pivot.
You have already learnt the first option in an earlier section. And this is preferable to the second option, as you will find at the end of this section. However, you should know how to copy data from clipboard into Power Pivot.
Suppose you have data in a word document as follows −
Word is not a data source for Power Pivot. Therefore, perform the following −
The Paste Preview dialog box appears.
The data copied into the clipboard will be pasted into a new data table in Power Pivot, with the tab − Word-Employee table.
Suppose, you want to replace this table with new content.
The Paste Preview dialog box appears. Verify the contents that you are using for replace.
Click OK.
As you can observe, the contents of the data table in Power Pivot are replaced by the contents in the clipboard.
Suppose you want to add two new rows of data to a data table. In the table in the Word document, you have the two news rows.
Click OK to proceed.
As you can observe, the contents of the data table in Power Pivot are appended with the contents in the clipboard.
In the beginning of this section, we have said that copying data to an excel file and using linked table is better than copying from clipboard.
This is because of the following reasons −
If you use linked table, you know the source of the data. On the other hand, you will not know the source of the data later or if it is used by a different person.
You have tracking information in the Word file, such as when the data is replaced and when the data is appended. However, there is no way of copying that information to Power Pivot. If you copy the data first to an excel file, you can preserve that information for later use.
While copying from clipboard, if you want to add some comments, you cannot do so. If you copy to Excel file first, you can insert comments in your Excel table that will be linked to the Power Pivot.
There is no way to refresh the data copied from clipboard. If the data is from a linked table, you can always ensure that the data is updated.
Refreshing Data in Power Pivot
You can refresh the data imported from the external data sources at any point of time.
If you want to refresh only one data table in the Power Pivot, do the following −
If you want to refresh all the data tables in the Power Pivot, do the following −
