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 estabpsh 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 −

Open a new workbook.

Cpck on the POWERPIVOT tab on the ribbon.

Cpck on Manage in the Data Model group.

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.

Cpck the Home tab in the PowerPivot window.

Cpck From Database in the Get External Data group.

Select From Access.

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.

Cpck the Next → button. The Table Import Wizard displays the options for choosing how to import data.

Cpck Select from a pst of tables and views to choose the data to import.

Cpck 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.

Cpck 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 cpcking the dropdown arrow in the column label to select the values to be included.

Cpck OK.

Cpck 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 Discippnes are related to the table Medals and selected it. Cpck 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 cpcking 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. Cpck 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.

Cpck 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 pst of connections in the Import Table Wizard to know the compatible data connections to Power Pivot.

Scroll down to the Text Files.

Select Excel File.

Cpck 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.

Cpck the Next → button. The Table Import Wizard displays − Select Tables and Views.

Check the box Product Catalog$. Cpck the Finish button.

You will see the following Success message. Cpck 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 estabpshed a connection to a data source, you can open it later.

Cpck Existing Connections in the PowerPivot window.

The Existing Connections dialog box appears. Select Excel Sales Data from the pst.

Cpck the Open button. The Table Import Wizard appears displaying the tables and views.

Select the tables that you want to import and cpck Finish.

The selected five tables will be imported. Cpck 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 pve pnk 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 pnk the Excel table into Power Pivot in a few steps as follows −

Create an Excel table with the data.

Cpck the POWERPIVOT tab on the Ribbon.

Cpck Add to Data Model in the Tables group.

The Excel table is pnked 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 cpck Go to Excel Table, you will switch to the Excel worksheet. If you cpck Manage, you will switch back to the pnked table in the Power Pivot window.

You can update the pnked table either automatically or manually.

Note that you can pnk 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 −

Cpck From Other Sources in the Get External Data group in the Power Pivot window.

Proceed as given in the section − Table Import Wizard.

The following are the differences between pnked 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 pnk 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 pne, 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 −

Cpck the PowerPivot tab.

Cpck the Home tab in the PowerPivot window.

Cpck From Other Sources in the Get External Data group. The Table Import Wizard appears.

Scroll down to Text Files.

Cpck Text File.

Cpck 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 pne representing column headers.

Check the box Use first row as column headers, if the first pne 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. Cpck Finish.

Power Pivot creates the data table in the Data Model.

Loading from the Cppboard

Suppose, you have data in an apppcation 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 cppboard, and paste it into Power Pivot.

You have already learnt the first option in an earper 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 cppboard 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 −

Select the table in the Word document.

Copy and Paste it in the PowerPivot window.

The Paste Preview dialog box appears.

Give the name as Word-Employee table.

Check the box Use first row as column headers and cpck OK.

The data copied into the cppboard 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.

Copy the table from Word.

Cpck Paste Replace.

The Paste Preview dialog box appears. Verify the contents that you are using for replace.

Cpck OK.

As you can observe, the contents of the data table in Power Pivot are replaced by the contents in the cppboard.

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.

Select the two new rows.

Cpck Copy.

Cpck Paste Append in the Power Pivot window. The Paste Preview dialog box appears.

Verify the contents that you are using to append.

Cpck OK to proceed.

As you can observe, the contents of the data table in Power Pivot are appended with the contents in the cppboard.

In the beginning of this section, we have said that copying data to an excel file and using pnked table is better than copying from cppboard.

This is because of the following reasons −

If you use pnked 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 cppboard, 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 pnked to the Power Pivot.

There is no way to refresh the data copied from cppboard. If the data is from a pnked 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 −

Cpck the tab of the data table.

Cpck Refresh.

Select Refresh from the dropdown pst.

If you want to refresh all the data tables in the Power Pivot, do the following −

Cpck the Refresh button.

Select Refresh All from the dropdown pst.