Figure 963 The pivot table works if you use fields from the joiner tables.

FIVE REASONS TO USE POWER PIVOT

WHY ISN’T POWER PIVOT TAB IN THE RIBBON?

Problem: I have a version of Excel 2013 that is supposed to have Power Pivot, Power Map, Power Query, but they are not in the ribbon.

Strategy: Go to File, Options, Add-Ins. In the bottom of the dialog open the Manage dropdown and choose

COM Add-ins. Click Go…

Add checkmarks to Power Pivot, Power Query, and/or Power Map.

GET EXCEL DATA INTO POWER PIVOT

Problem: How do I get my Excel data into Power Pivot?

Convert your Excel data to a table and then link the table to Power Pivot.

First, convert your dataset to a table by selecting one cell and pressing Ctrl+T. Excel will ask you to con- firm that your data has headers. Click OK. On the Table Tools Design tab, enter a new name for the table on the left side of the ribbon. This name will carry through to Power Pivot and be used in formulas later, so keep it short and easy to spell.

On the Power Pivot tab, choose Add to Data Model in Excel 2013 or Create Linked Table in Excel 2010.

Say you want to link from the ProdID field in the Fact table to the ProdID field in the Products table. Fol- low these steps:

1. Go to the Power Pivot window.

2. Click on the sheet tab for Fact

3. Place the cell pointer anywhere in the ProdID field.

4. Go to the Design tab in the Power Pivot ribbon. Select Create Relationship.

5. There are four fields to fill in. The first two fields area already filled in because of steps 2 & 3. 6. Open the Related Lookup Table dropdown and choose Products.

7. In most cases, Power Pivot will automatically fill in ProdID for the fourth field. If it does not, open the last dropdown and choose the ProdID field.

Another way to build a relationship is through the Diagram view. On the Home tab in Power Pivot, click Diagram View. Drag from the Date field in the Fact table to the Date field in the Date Table to establish a relationship.

3

Figure 967 Create relationships in Diagram View.

Gotcha: This diagram view acts differently than the one in Microsoft Access. After you have created re- lationships, the arrows generically point from one table to another. They do not point specifically to the linked field. To see the fields, you have to click on an arrow and the fields will be outline in blue.

Gotcha: Diagram view is slow and clunky. I feel like my computer is going to crash when I use it. I really prefer the two-click ease of building relationships discussed on the previous page.

To get back to the grid view, click the Data View icon in the Home tab.

SORT MONTH NAME BY MONTH NUMBER

Problem: Regular pivot tables use the Custom Lists dialog to automatically sort months into Jan, Feb, Mar sequence. Power Pivot doesn’t seem to be aware of Custom Lists and sorts into the alphabetic se- quence of Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar. May, Nov, Oct, Sep.

Strategy: There is an eight-click workaround in the Excel interface, but by your second week of using

Power Pivot, you will resign yourself to the fact that you need to have fields in your model called Month- Number and MonthName.

Click in the MonthName column. On the Power Pivot ribbon, go to the Home Tab. Select the Sort By Col- umn icon. In the Sort by Column dialog, indicate that you want to Sort MonthName by MonthNo.

Figure 968 It is annoying, but you will eventually accept this extra step.

CREATE A CALENDAR TABLE

One downside of Power Pivot is the inability to group daily dates up to months and years. The common workaround is to build a lookup table that contains every daily date from the earliest date to the latest date in your data.

Start with a Date heading in A1. Add the first date in A2. Grab the fill handle and drag down until you get to the last date in your data. Add additional columns as needed:

●● Year =YEAR(A2)

●● Month =MONTH(A2)

●● MonthName = TEXT(A2,”MMMM”)

●● Weekday: =WEEKDAY(A2,1)

●● WeekdayName: =TEXT(A2,”DDDD”)

Make this data set into a table and add it to the data model. Relate it to your Fact table.

The date table makes it possible to group daily dates to months or years. It will also make the Time Intel- ligence calculated fields in the pivot table easier to use.

THE FORMULAS ARE CALLED DAX

Power Pivot introduces a new formula language called Data Analysis eXpressions or DAX. When you see a Power Pivot demo, the 2 million rows and the joining tables look impressive. But it turns out that DAX is the really jewel in Power Pivot.

DAX is used in two places. DAX is used to add new columns in the Power Pivot grid. When used in this way, it is 99% similar to the functions that you know and love in regular Excel. The real power in DAX is when you add new calculated fields to the resulting pivot table.

While I am going to cover some essential DAX examples here, the my friend Rob Collie from Power Piv- otPro.com has written the essential title on understanding DAX. His book is Power Pivot and Power BI.

ADDING CALCULATIONS IN THE POWER PIVOT GRID

DAX shares 81 functions with Excel, so if you are proficient with Excel functions, you should have little problem working in DAX. There are two functions in DAX that differ from the equivalent function in Excel

- FORMAT replaces TEXT and RELATED replaces VLOOKUP.

The first blank column in the Power Pivot window is called Add Column. Click in any cell in that column. Type an equals sign and enter your formula. For example, type =YEAR( and then, using the mouse, click on the Year field. Type a closing parentheses and press Enter. Your formula will populate all the way down the grid.