Continuing with our look at PowerPivot. You should now have PowerPivot installed as an add-on to Excel, and configured some data connections to it. My most recent blog post showed how we can create relationships between disparate data imported or connected to PowerPivot. Now, with some data available, we can start to create a Pivot Table.
Creating PivotTables with PowerPivot
To create a PivotTable from the tables in the PowerPivot window, you can either use the icon on the PowerPivot tab of your Excel Ribbon, or on the Home tab of the PowerPivot window Ribbon. Note that you don’t use the “standard” PivotTable icon from the Excel Insert tab, that’ll create a traditional PivotTable without all the PowerPivot goodness. You need the PivotTable icon on the PowerPivot tab, which is greyed-out if you don’t have any data in the PowerPivot.
Clicking this PivotTable icon gets the usual dialog about where to place the Pivot Table, it can be positioned on an existing sheet, or open a new sheet.
Then on your Excel worksheet, you’ll will get the familiar PivotTable placeholder, however the Field List that displays is specific to a PowerPivot PivotTable and is not the default PivotTable field list.
The PowerPivot Table Field List lists all the tables from the PowerPivot window, with all the fields from each table listed.
You construct a PowerPivot Table in much the same way as a normal PivotTable in Excel i.e. by dragging fields from the Field List into one of the PivotTable areas. Notice that you can use fields from any of your related tables.
So, what is different about a PowerPivot table ?
If you look at the Value Field Settings for a values field in a PowerPivot Table, you’ll see that the “Summarize value field by” option is greyed out. Instead, you will need to use the dropdown on the field in the values area of the PowerPivot Table and then select “Summarize By”.
You will also notice that all the Grouping options on the Options tab of the PivotTable Tools are also greyed out. In PowerPivot, to organise your data into ad-hoc groups, you need to create these groups as Calculated Columns in the PowerPivot window. If you want to group your data by Year or Month, for instance, you have to create a Date Table as explained in a later post.
Of course, the real big difference is the ability to mix data from related tables and then create powerful calculations (called Measures) in the Values area of a PowerPivot Table. But before we get to Calculations there are a few other parts of the basic PowerPivot Pivot table to explore.
Slicers work much the same as in a normal PivotTable except that you have two areas in the PowerPivot Field list to drag fields into (Slicers Vertical/Slicers Horizontal). PowerPivot Slicers sit in dynamic containers that rearrange themselves it as you move the fields in the Field list.
There are three areas in PowerPivot that may require a refresh when the base data changes:-
1. Source data refresh
2. Data Model refresh
3. Pivot data refresh (or Update)
Source Data Refresh
If your data is from an external data source, you will need to use the Refresh icon on the Home tab of the PowerPivot Window.
If your data is from linked tables in the Excel Workbook, by default these are refreshed automatically when the data changes. Just move the focus to the PowerPivot window for this to happen. Linked tables can also be updated manually from the PowerPivot window or from the Excel window.
PowerPivot Data Model Refresh
If you make any changes in the PowerPivot Window that affects the PowerPivot data model (e.g. adding a calculated column or deleting a column or entire table) then the PowerPivot Field List will prompt you to Refresh the Field List.
If any of your source data has changed in the PowerPivot window (either in a linked table or you’ve refreshed an external data source), you will need to refresh the PowerPivot itself. You can use the Refresh icon on the Options tab of the PivotTable tools. However, this refreshes ALL Pivot Tables in your workbook and therefore may take some time. A Pivot table will update automatically whenever you make a change in the Row Labels, Column Labels or Values areas of the Pivot Table Field list.
So, there’s a lot of things you can do with the PowerPivot Table, powerfully linking and analysing disparate data, but we’ve not yet discussed calculated fields, so play about with your Pivot table a bit now, and we’ll look at calculated fields next.
Burningsuit provide Training and Consultancy on PowerPivot and all aspects of Excel and Microsoft applications. We specialise in on-site or on-line instructor lead Training. Contact us to find out how we can help you and your business or organisation be more productive and effective.