This chapter is from the book
This chapter is from the book
This chapter is from the book
Getting Your Data into PowerPivot
In this example, you will be importing two different data sets into PowerPivot. You will be importing the text file. For the Excel data about the stores, you can either copy and paste into PowerPivot, or define a table and link it to PowerPivot. For this example, a simple copy and paste will work.
|
|
See Chapter 4, "Getting Your Data into PowerPivot," for details on using the linked table method. |
Decide on a Sequence for Importing
You should import the main transaction table first. In this case, the main transaction table is the 1.8 million row file with sales information. After that file is imported, you can import the lookup table information.
You should follow this sequence for two reasons:
- In the PowerPivot Field List, the tables are presented in the order that they were imported. You will want your main table at the top of the list when building pivot tables.
- The PowerPivot relationship detection logic expects the main table to be first.
So, for this example, you will be loading the text file first, and then pasting the stores data into PowerPivot.
Import a Text File
To import the 1.8 million row file into PowerPivot, follow these steps:
- Select the PowerPivot tab in Excel 2010.
- Select the PowerPivot Window icon. A new PowerPivot application window will appear. PowerPivot offers two ribbon tabs: Home and Design. The left side of the Home tab is shown in Figure 2.4.
Figure 2.4 The Home tab of the PowerPivot application.
- You want to import your main table first. This will be the large CSV file shown in Figure 2.2. From the Get External Data group, select From Text. PowerPivot shows the Table Import Wizard (see Figure 2.5).
Figure 2.5 Preparing to import a text file.
- Because your first row of the file contains headers, select the Use First Row as Column Headers check box.
- Click the Browse button and locate your text file. PowerPivot will pause for a second and fetch the first 50 rows of data from the text file to provide a data preview, as shown in Figure 2.6.
Figure 2.6 PowerPivot provides a 50-row preview.
- If there are any columns that you don't need to import, clear them.
- Note that there are filter drop-downs for each field. You can actually sort and filter this 1.8 million row data set here, although it will be slower than in a few steps from now. If you open a filter field, PowerPivot will scan the entire file to build the list of filters, as shown in Figure 2.7. You can choose to exclude certain values from the import.
Figure 2.7 To conserve file size, you can choose to exclude certain divisions from the import.
- Click Finish and PowerPivot will begin loading the file into memory. The wizard will show how many rows have been fetched so far (see Figure 2.8). Loading data can take a long time. If you would need to interrupt the import, the Stop Import button is available at the bottom of the wizard.
Figure 2.8 PowerPivot provides an update on how the import is progressing.
- When the file is imported, the wizard confirms how many rows have been imported, as shown in Figure 2.9. Click Close to return to the PowerPivot window.
Figure 2.9 These 1.8 million rows are now stored in your Excel file.
- The 1.8 million row data set is shown in the PowerPivot Window. Go ahead. Grab the vertical scrollbar and scroll through the records. You can also sort, change the number format, or filter (see Figure 2.10).
Figure 2.10 1.8 million records are in a grid that feels a lot like Excel.
)
)
)
)
)
)
)
|
|
To see a demo of importing data, search for PowerPivot Data Analyst 1 at YouTube. |
The filters in PowerPivot are not as powerful as the new filters introduced in Excel 2007. In particular, the date columns do not show a hierarchical filter where you can choose a year or month.
If you right-click a column heading, a menu appears where you can rename, freeze, copy, hide, unhide the columns (see Figure 2.11).
)
Figure 2.11 Right-click a column to rename it.
Bottom line: You have 1.8 million records that you can sort, filter, and later, pivot. This is going to be cool.
Add Excel Data by Copying and Pasting
Next, you want to add your store information to PowerPivot. As mentioned previously, you could either link to the Excel data or copy and paste. For this example, a simple copy and paste will work.
|
|
See Chapter 4 for more information on linking. |
To return to the Excel workbook from PowerPivot, you can press Alt+1 or click the tiny Excel icon at the top left of the PowerPivot window (see Figure 2.12).
)
Figure 2.12 Use this icon to return to your Excel workbook.
- Open your Stores table in Excel.
- Select the data with Ctrl+*.
- Copy it with Ctrl+C.
- Click the PowerPivot Window icon. PowerPivot returns and you see your 1.8 million row data set.
- Click the Paste icon on the left side of the PowerPivot Home tab. You will see a Paste Preview window.
- Give the new table a better name than Table. Perhaps StoreInfo (see Figure 2.13). Click OK.
Figure 2.13 Give the pasted table a name.
)
){kind=link}
You will now see the store information in a new StoreInfo tab at the bottom of the screen. Notice that there are now two worksheet tabs in PowerPivot, as shown in Figure 2.14.
)
Figure 2.14 You now have two unrelated tables in the PowerPivot window.