Hands on Assignment 3

1

Mashing Up Data with PowerPivot in Excel 2013 and Up*

Installing PowerPivot PowerPivot is included in Excel 2013, Excel 2016 (Office 365 Professional version) as a free add-in program brought to you by the SQL Server Analysis Services team at Microsoft. To improve Excel as a Business Intelligence tool, PowerPivot makes it possible to do jaw-dropping analyses in Excel. This set of hands-0n 4 instructions is for using Power Pivot in Excel 2013. To start PowerPivot in Excel 2013 and Excel 2016, please follow the instructions at http://office.microsoft.com/en-us/excel-help/start-power- pivot-in-microsoft-excel-2013-add-in-HA102837097.aspx.

1. Go to File > Options > Add-Ins.

2. In the Manage box, click COM Add-ins> Go.

3. Check the Microsoft Office Power Pivot in Microsoft Excel 2013 box, and then

click OK. If you have other versions of the Power Pivot add-in installed, those

versions are also listed in the COM Add-ins list. Be sure to select the Power Pivot

add-in for Excel 2013.

The ribbon now has a Power Pivot tab.

Add Excel Data By Linking There is a small Excel file that maps StoreID to store name and other relevant data. Moreover, you want to make sure that if the original Excel worksheet changes, those changes make it through to PowerPivot. To do this, follow the steps below:

1. Please open the Excel file Stores. 2. Select one cell in the worksheet and press Ctrl+T. Excel asks you to confirm the

extent of your table and if your data has headers.

2

3. The table gets a default format. You can use the Table Tools Design tab to change that format if the current one does not look good to you.

4. On the left side of the ribbon, you see that this table is called Table1. Type a new name such as StoreInfo

5. On the PowerPivot tab, select Add to Data Model as shown below, the table appears in the PowerPivot window. A link icon should show in front of the sheet tab name

Building a PowerPivot Report This part will walk you through your first PowerPivot data mash up. In this example, you create a report that merges a 1.8 million row file with a store identifying data in Excel. To import the big data file into PowerPivot, follow these steps: 1. Select the PowerPivot tab in Excel 2013 or 2016. 2. Select the Manage icon, the first on the blue ribbon. A new PowerPivot application

window appears. PowerPivot offers two tabs: Home and Design

3

3. You want to import your main table first. This is a large data file. From the Get External Data group select From Other Sources, then select Text File. PowerPivot shows the Table Import Wizard.

4. Select a Friendly Connection Name, such as Sales History. Click the Browse button and locate your text file, Demo. Make sure that your Table Import Wizard looks the same as the image below

4

5. If there are any columns that you do not need to import, clear those check boxes. The file is going to be read into memory. Click Finish and PowerPivot begins loading the file into memory. The Wizard shows how many rows have been fetched so far

5

6. Click Close to return to the PowerPivot window. 7. The data set is shown in the PowerPivot Window. Grab the vertical scroll bar and

scroll through the records. You can also Sort, change the number format, or filter

6

Define Relationship 6. You need to link from one column in your main table to a column in another table.

To simplify the relationship process, navigate to your main table, Demo, and select a cell in the column from which you are linking.

7. Click on the Design tab in the PowerPivot ribbon. 8. Select Create Relationship. The Create Relationship dialog appears. By

default, the selected table and column appears in the first two fields, as shown below.

7

9. If you skipped a step and the correct table is not shown in the Table drop-down, then select Demo from the Table drop-down.

10. If you did not select the correct column in the previous step, then open the Column drop-down to select StoreID.

11. Open the Related Lookup table drop-down. Select StoreInfo. 12. Because the column names match, PowerPivot automatically changes the

Related Lookup Column to say StoreID. This simple dialog replaces the VLOOKUP function.

13. Click Create. You have now created a relationship between the two tables. 14. Click Manage Relationships on the ribbon to check it out.

Add Calculated Columns Using DAX Before building the pivot table, use DAX (Data Analysis Expression) formula language to add a new calculated column to the Demo table. Follow these steps to add a Year field to the Demo table:

1. Click on the Demo worksheet tab at the bottom of the PowerPivot window. 2. The column to the right of Revenue has an Add Column heading. Click in the first

cell of this blank column.

3. Click the icon to the left of the formula bar. The Insert Function dialog appears with categories for All, Date&Time, Math&Trig, Statistical, Text, Logical, and Filter. Select Date&Time from the drop-down. You instantly notice that this is not the same list of functions in Excel.

Luckily, some familiar old functions are in the list as well. Scroll down and select the YEAR function. Click on the first date in the Date column. PowerPivot proposes a formula of =year(Demo[date]. Complete the formula by typing a closing parenthesis and pressing Enter. Excel fills in the column with the year associated with the date, as shown below.

8

4. Right-click the column heading and select Rename Column. Type a name such as Year. This method allows you to add as many columns as you like.

Build A Pivot Table One of the advantages of PowerPivot is that multiple tables can share the same data and slicers. Open the PivotTable drop-down on the Home tab of the PowerPivot ribbon. You have many options beyond a single table or chart. Follow these steps:

1. Select PivotTable. You now see the PowerPivot tab back in the Excel window. 2. Choose to put the pivot table on a new worksheet. The PowerPivot Field List is a

third variation of the pivot table field list. It is actually a new entry in the Task Pane. Both tables are available in the top of the Field List. The main table is expanded to show the field names, but you can expand the other table and add those fields to this pivot table. Two new sections in the drop zones offer vertical or horizontal slicers. Because you are in a pivot table, the PivotTable tabs are available.

3. Select Revenue from Demo in the PowerPivot Field List. Expand the StoreInfo table. Select Region from the StoreInfo table. Excel builds a pivot table showing sales by region. At this point, you have a pivot table from 1.8 million rows of data with a virtual link to a lookup table.

9

4. You might want to go to the PivotTable Tools tabs to further format the pivot table. Please apply a currency format and rename the Sum of Revenue field and Row Labels, choose a format with banded rows, and so on.

Slicers in PowerPivot To show some more features of the PowerPivot pivot table, add some slicer functionality. The slicers in PowerPivot are slightly different from slicers in regular Excel.

1. Select Analyze and then select Insert Slicer. You will notice that box for Slicer appear. This is used for both Vertical and Horizontal Slicers.

10

2. They are great for long lists that might need a scroll bar. Horizontal slicers go

above your pivot table. 3. Select year and create the slicer. The years appear in a small slicer surrounded

by a big box, as shown below. Never try to make that big box smaller

11

3. Follow step 1 to add additional slicers 4. Add Division and Era to the Slicers. Resize the slicer as per need. It will look like

below

5. Add Mall Developer to the slicer, place it on left of the pivot table as it has long list of relatively long names.

6. Slicers work the same as they do in regular Excel pivot tables. Click one item to select it. Ctrl+click additional items to select them as well. See the changes to the pivot table. You can resize the boxes if you want the slicers to take up more or less room. Click the box once and resizing handles appear. To format the slicers, you have to click on the slicer.

7. Save and close the Excel file. Two Kinds of DAX Calculations A DAX function can add a calculated column to a table in the PowerPivot window. The RELATED function can also be used in a calculated column to grab a value from a different table. DAX can also be used to create new measures in the pivot table. These functions do not calculate a single cell value. They are all aggregate functions that calculate a value for the filtered rows behind any cell in the pivot table.

12

Using RELATED() to Base a Column Calculation on Another Table The next several examples make use of a file called WeatherMashup.xlsx. This file started out with generic sales by day and by store data set (Sales datasheet). A company sells products both in a mall location and at an airport. The two stores are less than 10 miles apart, but they might show different sales trends. Suppose you downloaded weather data for the three years of daily dates. Several examples in the DAX Measure calculation mash up the sales and weather data to look for trends.

1. Please open the workbook WeatherMashup.xlsx and link the Excel table Sales to the same table in PowerPivot window (Hint: you’ve learned it in the previous part).

2. Use DAX to create a calculated column, WeekdayName, use =FORMAT(Sales[Date],”dddd”) to convert the date to the day of the week.

3. Use DAX to create a calculated column, WeekdayID, use =WEEKDAY(Sales[Date],2). The 2 argument matches the same function in Excel. 2 numbers the days starting with 1 for Monday through 7 for Sunday.

4. Use DAX to create a calculated column, LocationDay, use =CONCATENATE(Sales[Location],Sales[Date]). You see this used later when calculating the distinct number of store days.

5. To calculate sales per store associate, the solution is to use the Related() function. This function tells DAX to divide by the one value that is related to a particular locationday. To accomplish this, a relationship has to be established between Sales and the Staffing table.

6. For this purpose, you need to create a column, LocationWeekDay in Sales, using the formula =Concatenate (Sales[Location],Sales[WeekDayName]).

7. Link Sales and Staffing by LocationWeekDay and LocationWeekday. This time, you are creating a relationship between two tables. Create Relationship icon is in Design menu.

8. Create another column in Sales, SalesPerPerson, use the formula = Sales[Net Sales]/Related(Staffing[StaffLevel]).

9. Hint: You need to create relationships between all the data tables, before you are able to move on. Sales and Weather are related by Date. Sales and Weekdays are related by WeekdayID.

Count Distinct Using DAX DAX lets you count how many distinct values meet the filter. To create a new calculated field in DAX, use the Calculated Fields icon in the PowerPivot tab. A calculated field is a formula that is created specifically for use in a PivotTable (or PivotChart) that uses PowerPivot data. You have to create a pivot table as in the following image, before you can create a new calculated field

13

1. When you click Calculated Fields and then click on New Calculated Field, you get the Calculated Fields Settings dialog box open. The Table name should be the base table where your main numerical data is located. Change the first drop- down from Weather to Sales if necessary.

2. For the Calculated Field Name, use a name such as DayCount. 3. Use the same name for Description. 4. Calculated Fields are always aggregate functions, not cell-level functions. Thus,

you must use an aggregate function such as SUM or COUNTROWS. 5. The magic function here is Distinct(Sales[Date]). For any cell in the pivot table,

the distinct function returns a list of the distinct values for the rows that match the filter.

6. After typing the formula, click the Check Formula button to make sure that your syntax is correct.

14

7. You could build SalesPerDay with a single formula: =SUM(Sales[Net Sales])/COUNTROWS(Distinct(Sales[Date])). The figure below shows Sales Per Day based on the amount of rain and the location.

15

16

There is something wrong with the grand totals in the image above. For the airport, the average of $2665 in sales per day is accurate. However, if the averages for the airport and for the mall are correct, the grand total in the lower right corner should not be $5833. How about adding Day Count Column into the pivot table?

The airport location was open for all those years. The mall location opened late in 2006 so there are fewer days for the mall location. The airport is open on Christmas, but the mall is not. Thus, there many days where only one store is open. The 2nd column shows total sales of both stores. The Day Count column counts a day when either one store or the other was open. Thus, both stores did sell $6.3 million over the course of the data set. However, because both stores were not open for the entire period, the calculation of $6.3 million divided by 1086 days is wrong. The solution is to count the distinct number of a concatenated column of location and date. We have to count the location days for each store and calculate the sales per store per day.

1. Create a new DAX Calculated Field with the formula, LocationDayCount=CountRows(Distinct(Sales[LocationDay])).

2. Create a new DAX Calculated Field with the formula, SalesPerStorePerDay= SUM(Sales[Net Sales])/CountRows(Distinct(Sales[LocationDay])).

3. These two new measures should produce the exact same results for the airport or mall sales per day, the improvement is that the pivot table should show the true average sales per store per day.

17

When you complete this assignment (your data table should be longer than what shows in the above image), format the values in their proper format, and save all the files into a zip folder, each with your name initials as part of the file name (the pattern should be <OriginalFileName_YourNameInitials>). Submit the zip folder to the relevant assignment submission dropbox in Learn 9. _______________________ *: This assignment is adapted from Chapter 10 in Pivot Table Data Crunching: Microsoft Excel 2010 written by Bill Jelen, Michael Alexander, 2013, Que Publishing, Indianapolis, IN, USA.