The Promise of PowerPivot in Excel 2010
This chapter is from the book
This chapter is from the book
This chapter is from the book
This chapter walks you through one process of building a PowerPivot report. You will learn how simple it is to merge data from two sources in a single PowerPivot pivot table.
You can download these data sets from http://www.MrExcel.com/powerpivotbookdata.html.
Before trying these steps in this chapter, make sure that you have installed the PowerPivot client in Excel 2010 as described in Chapter 1, "Downloading and Installing PowerPivot."
Preparing Your Data for PowerPivot
If you are using your own data in PowerPivot, you need to ensure that your data is formatted appropriately. PowerPivot can deal with many different types of data. But unfortunately, people and software vendors do a lot of weird stuff with their data.
One of my favorite large data sets comes from a cash register vendor. They produce these large 1.8 million row data sets as flat text files and send them to my client every day.
In Figure 2.1, you can see the top of the data set. Row 1 contains the words "Run for:" and the date. Row 2 is blank. Row 3 has headings. Row 4 has underscores. Seriously? Underscores? Who are those really for?
)
Figure 2.1 PowerPivot has no problem with 1.8 million rows, but it can't deal with the extra rows at the top.
PowerPivot cannot deal with three of those first four rows. PowerPivot can deal with the field headings, but the Run For, the blank row, and the underscores have to go. This either means a request sent to the software vendor or a daily trip through Notepad to delete those rows.
If you want PowerPivot to treat a column as a date column, then 100% of the values in that column have to be dates. This same vendor loves to use dates of **/**/**** for new items that are not yet in inventory.
The point is that any date is going to have strange characteristics that you will have to deal with. Work the IT department or your vendor to get the data cleaned up for PowerPivot.
In Figure 2.2, removing three headings solves the problem.
)
Figure 2.2 One row of headings, followed by data records.
If your data is in Excel, you want to follow the same format. One row of headings followed by data rows. No blank columns. No blank rows. Make sure that any date columns are 100% filled with dates.
In the text file, there is only a store number. It would be nice to add store name, region, and so on. In Figure 2.3, a small 150-row data set provides information about each store.
)
Figure 2.3 Identifying information for each store.