BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
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:
|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:
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:
Right-click on any columns to hide them from client tools.
Already the pivot table field list is looking better!
|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:
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:
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:
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:
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:
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:
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.