17. Mashing Up Data with PowerPivot
A revolutionary add-in called PowerPivot, which debuted in Excel 2010, enabled you to build pivot tables from multiple tables. For Excel 2016, that core functionality of PowerPivot is built directly into Excel. This chapter starts by showing you how anyone with Excel 2016 can build pivot tables from multiple tables.
Certain versions of Excel 2016 also ship with the full PowerPivot add-in. If you have one of these versions, you can activate the add-in and access additional tools, such as a powerful Data Analysis Expressions (DAX) formula language. The latter part of the chapter describes the additional benefits of PowerPivot.
Joining Multiple Tables Using the Data Model
When you see the term Data Model in Excel 2016, it’s Microsoft’s way of saying you are using PowerPivot without calling it PowerPivot.
Preparing Data for Use in the Data Model
When you are planning to use the Data Model to join multiple tables, you should always convert your Excel ranges to tables before you begin. You theoretically do not have to convert the ranges to tables, but it is far easier if you convert the ranges to tables and give the tables a name. If you don’t convert the ranges to tables first, Excel secretly does it in the background and gives your tables meaningless names such as “Range.”
Figure 17.1 shows two ranges in Excel. Columns A:H contain a transactional data set named Sales. Columns J:K contain a customer lookup table called Sector. You would like to create a pivot table showing revenue by sector.
Excel gurus are thinking, “Why don’t you do a VLOOKUP to join the tables?” PowerPivot lets you avoid the VLOOKUP. In this case, the tables are small and a VLOOKUP would calculate quickly. However, imagine that you have a million records in the transactional table and 10 columns in the lookup table. The VLOOKUP solution quickly becomes unwieldy. The PowerPivot engine available in the Data Model can join the tables without the overhead of VLOOKUP.
Building a Pivot Table from the Data Model
Choose one cell in the first data set and select Insert, PivotTable from the ribbon. In the Create PivotTable dialog, the table name appears. Choose the check box for Add This Data to the Data Model. Then click OK.
You get a new blank workbook with a PivotTable icon in A3:C20, just like with a regular pivot table. The PivotTable Fields task pane displays, but this is a slightly different version. Note the addition of the line with the choice of Active or All at the top of the pane in Figure 17.2.
Adding the Second Table and Defining a Relationship
Focus on the PivotTable Fields task pane. In the second line of the pane, you have a choice for Active or All. Choose All. You now see a list of each defined table in the Excel workbook. There is a triangle icon next to each table.
Click the triangle next to Sectors to see a list of the available fields in the Sectors table. Drag the Sector field from the top of the PivotTable Field List to the Columns area in the bottom of the PivotTable Field List.
You will notice three things:
The bottom of the PivotTable Field List is now showing fields from two different tables.
The pivot table is showing sectors, but the numbers are identical and clearly wrong in each column (see Figure 17.2).
A yellow warning appears in the top of the PivotTable Field List indicating that relationships between tables may be needed and offering buttons for Auto-Detect and Create.
Click the Auto-Detect button. Excel might get the relationship correct. If not, it is easy to use the Create Relationship dialog to define the relationship (see Figure 17.3).
After defining the relationship, you have successfully completed the Data Model. The pivot table updates with correct numbers, as shown in Figure 17.4.
Figure 17.4 Without doing a VLOOKUP, you’ve successfully joined data from two tables in this report.
Understanding the Limitations of the Data Model
On the face of it, this new Data Model pivot table feels like a regular pivot table. But they are not the same. By using the Data Model, you’ve just taken your regular Excel data and moved it to a tabular model that is considered external to Excel. There are some benefits available to pivot tables built on the Data Model, but there are also some annoying limitations.
Here are some of the benefits:
The Value Field Settings dialog now offers a Distinct Count option for Summarize Values By. It no longer offers Product as a calculation, but I don’t think many people ever used Product, so that is fine.
Suppose that you’ve filtered your report to show the top 10 customers but would like the total for all customers. Go to Design, Subtotals. The option for Include Filtered Items in Totals is now available. Choose this, and the pivot table shows only 10 customers but shows the totals from all customers. This avoids the need to use the AutoFilter hack described in the previous chapter.
You can now use Named Sets to produce asynchronous reports. Suppose that you want to show Actuals for last year and Budget for next year. A Named Set lets you define a group of fields, such as [Prior Year | Actuals] and [Current Year | Budget]. Named Sets don’t work with regular pivot tables. Take your data through the Data Model, and you can now define Named Sets.
Use Analyze, OLAP Tools, Convert to Formulas to convert your entire pivot table to Cube Formulas.
These are four very specific benefits that are great in very narrow situations. If you are trying to perform a Distinct Count, you will love the benefits of the Data Model. However, there are some big drawbacks to using the Data Model. The following features are used frequently by pivot table fans, so you are more likely to be stung by these issues:
Strange drilldown—Usually, you can double-click a cell in a pivot table and see the rows that make up that cell. This now works with the Data Model, but will return only for the first 1,000 rows.
No calculated fields or calculated items—The Data Model does not support traditional calculated fields or calculated items. If you have PowerPivot, the DAX measures run circles around these old calculations. But if you don’t have PowerPivot, you would have to learn the MDX formula language to add calculations to the pivot table.
Benefits of Moving to PowerPivot
If your version of Excel 2016 includes the full PowerPivot add-in, there are several benefits:
More ways to get data into PowerPivot. More data sources, plus linked tables, copy and paste, and feeds.
DAX formula calculations, both in the grid and as a new calculated field called a measure. DAX is composed of 135 functions that enable you to do two types of calculations. You can use the 81 typical Excel functions to add a calculated column to a table in the PowerPoint window. Then you can use 54 functions to create a new measure in the pivot table. These 54 functions add incredible power to pivot tables.
Note
If you plan to deal with millions of records, you want to go with the 64-bit versions of Office and PowerPivot. The 64-bit version of Office can make use of memory sizes beyond the 4GB limit in 32-bit Windows.
More ways to create relationships, including a Diagram view to show relationships.
Capability for hiding or renaming columns.
Count Distinct Using DAX
DAX lets you count how many distinct values appear in a field. Typically, if you ask a pivot table to count the customer field, you will find out how many records there are. By changing the Value Field Settings to Distinct Count, you can find out how many unique customers fall into each category. (See Figure 17.5.)
Date Intelligence Using DAX
Calculated fields created with DAX can do things regular pivot tables cannot do. To create a new field using DAX, select one cell in your pivot table and choose PowerPivot, Measures, Create New Measure.
Figure 17.6 shows the completed measure. The base table should always be your main data table. The function CALCULATE is similar to SUMIFS, with one cool exception. Normally, a cell in a pivot table is filtered by the slicers, the row fields, and the column fields. In cell D21 of Figure 17.7, the row field is imposing a filter of 1/30/2019. The DAX measure is redefining the filter. By asking for all dates in DATESMTD(Sales[Date]), the MTDSales field returns all January 2019 dates up to and including the 30th of January.
To calculate sales for the same day last year, use DATEADD to move back one year:
Prior Year Sales
=CALCULATE(Sales[Sum of Revenue],
ALL(Sales[Date (Year)]),
DATEADD(Sales[Date],-1,YEAR))
After you define a calculated field, you can use that field in future calculations:
Prior Year MTD Sales
=CALCULATE([PriorYearSales],
DATESMTD(Sales[Date]))
And then:
MTDChangeOverLastYear
=[MTDSales]-[PriorYearMTDSales]
Figure 17.8 shows the results of those calculations.
Here is the beautiful thing: If your goal is to show MTD sales growth, you can remove all the intermediate calculations from the pivot table and show only the final calculated field.
Note
To learn DAX, check out Rob Collie’s book, DAX Formulas for PowerPivot (Holy Macro! Books, 2012) for numerous examples and tutorials.
Interactive Dashboards with Power View
One of Excel’s cross-town competitors is a product called Tableau. The Power View tool was introduced in Excel 2013 Pro Plus as an attempt to create dashboards as cool as those created by Tableau. The product felt like a first-version product in Excel 2013, and it has not improved in Excel 2016. The concepts introduced in Power View have been moved to Power BI Desktop, an online dashboarding tool. The Excel team will eventually work to improve these concepts in a later version of Excel.
In Excel 2016, Power View is hidden. You have to customize the ribbon and add Insert Power View Worksheet if you want to use Power View.
A Power View sheet gets inserted into your Excel workbook as a foreign object. No Excel formulas can point to cells on the Power View sheet. The data for your Power View dashboards will come from the PowerPivot Data Model stored in the workbook.
Elements on the dashboard can include tables, charts, and maps. Each element can start as a thumbnail that expands to full screen when you use the Pop-Out icon.
Although I’m not devoting a lot of space to this product that Microsoft apparently has lost interest in, I will point out a few tricks that are unique to Power View.
By default, every element is a filter for all other elements on the dashboard. If you have three charts and select the Excel 2010 wedge in the top-right chart, both of the other charts in Figure 17.9 update to gray out the information not pertaining to Excel 2010.
Power View is also great at displaying product pictures in the pivot table. Provided you have a field with the URL of that product, the pictures can display in the pivot table (see Figure 17.10).
