An overview of PowerPivot for Excel 2010
Part three of a six-part series of blogs

PowerPivot is a superb addition to Excel: it allows you to create pivot tables based on multiple tables taken from a variety of data sources, and its DAX language lets you report any statistic you want. This overview summarises how to get started with PowerPivot, and what it does.

  1. Getting started with PowerPivot for Excel 2010
  2. Stage 1 - Importing the tables we need
  3. Stage 2 - Creating a data model (this blog)
  4. Stage 3 - Creating a Pivot Table
  5. Stage 4 - creating measures using DAX
  6. Stage 5 - KPIs in PowerPivot for Excel

Posted by Andy Brown on 31 January 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Stage 2 - Creating a data model

If you try to create a pivot table now, this is what you'll see:

Create pivot table icon PowerPivot field list
Creating a pivot table The fields you'll see

A user of your pivot table might find this confusing.  There are some fields that they won't be interested in, they'll be confused by the fact that there are two tables, and the fields need better names.  We've also no way of grouping films by their decade of release.  Time to solve these problems!

The process of taking a techie set of tables and columns and simplifying it to make it usable is called "building a data model".

Hiding tables and columns

A user won't want to see two tables, so we'll hide the directors one:

Hiding table from client tools

You can right-click on any table to hide it from client tools (this means it won't show up in Excel pivot tables).


Likewise, a user won't want to see each director's id number or their release date, so we'll hide those:

Hiding columns from client tools

Right-click on any columns to hide them from client tools.


Already the pivot table field list is looking better!

Refreshing pivot table field list The fields left
When you bring in the changes ... ... there's not much left!

Creating calculated columns

We need to have the film's release decade and the director's name visible too.  We can get the film's release decade with a formula which is more or less the same as it would be in Excel.  Here's how to start your formula:

Creating year formula

When writing a formula, click on a column to include it (like using a cell reference in Excel).


The full formula should be as follows:

=left(year([ReleaseDate]),3) & "0s"

So if a film is released on 1st November 1998, for example, the year will be 1998, the first 3 characters will be 199 and the decade will be 1990s:

Decade formula in column

After typing in the formula, the values will appear for each row. You can then rename your column (from CalculatedColumn1 as here to Decade, perhaps).


To get the director's name, we'll use the RELATED function:

Typing RELATED function to get director name

Click at the top of a new column and start to create a formula as shown. You can pull in a column from any table related to the films one, whether directly or indirectly.


Here's the column when it's created:

The full formula for the director name

Notice how columns you've hidden from the client tools (ie Excel) appear greyed out.


The RELATED function is like a VLOOKUP function, but easier to write.

The final data model

If you now return to Excel, you'll see the final data model for our pivot table:

Final data model

After refreshing, you should see these columns. Much easier to use!


Now that we've imported our data and created a data model, it's time to create a pivot table.

This blog has 0 threads Add post