Book Image

Instant Creating Data Models with PowerPivot How-to

By : Taehyung Lee
Book Image

Instant Creating Data Models with PowerPivot How-to

By: Taehyung Lee

Overview of this book

Microsoft PowerPivot is a free software designed to enhance Microsoft Excel. It allows the user to make extensive use of his/her computer's powers to draw data from various sources, do analysis across millions of rows of data, and present results in a very unique format. Instant Creating Data Models with PowerPivot How-to is a concise and to-the-point guide that helps you get a jump start on using this strong business intelligence tool, while still working in an environment similar to Excel. You will begin with data generation and manipulation, learning a new feature at each step by building onto the old file, and ultimately creating a comprehensive file. Instant Creating Data Models with PowerPivot How-to will guide the user through database installation, importing data from various sources, creating pivot charts and tables, utilizing a unique feature of PowerPivot called slicers,adding custom columns, and setting custom relationships between data to generate the ultimate customized dataset for analysis. By the end of the book and all the sections of Microsoft PowerPivot for Excel, the reader will be fully experienced and ready to utilize this powerful software.
Table of Contents (7 chapters)

Importing data from other files (Simple)

PowerPivot has the ability to aggregate and connect data from multiple sources. We will import some data from the Web into PowerPivot, which we may use later on in conjunction with the data imported from the database.

How to do it...

  1. The web page contains historical GDP for each nation in the world, which can be simply downloaded as an Excel file. Open up the new Excel file using PowerPivot as shown in the following screenshot:

  2. Select both the tables, and check the resulting sheet. Save as GDPData.xlsx for later usage.

  3. Similarly, download the unemployment rate data as an XLS (Excel) file from, which contains the historical unemployment rate of each OECD nation. This will not import to PowerPivot directly as the file is in the .xls format, but is based on another format (XML). Open it in Excel and save as a CSV file.

  4. Use PowerPivot to import the CSV file and save as UnemploymentData.csv.


Downloading the sample files

You can download the sample files for all Packt books you have purchased from your account at If you purchased this book elsewhere, you can visit and register to have the files e-mailed directly to you.

How it works...

PowerPivot has a built-in convertor that converts the data from various sources into PowerPivot data. These results can be saved separately as we have just performed and can be aggregated together at a later stage.

Similar to the data from the database, the changes made to the source files (that is, Excel files) may be updated in the PowerPivot file by simply clicking the Refresh button.

There's more...

PowerPivot has limited support for certain sets of files, but these are more than enough for regular analysis.

Despite the fact that PowerPivot contains a tool called Importing from Text for the .txt and .csv files, and that we have manually created and used a .csv file, these files are not readily used anymore as most institutions now use Excel in providing data.