Boyd Air keeps a text file with performance codes and a Microsoft Access table with flight details. You import the data in Power Pivot to use the data model for a PivotTable.
[Student Learning Outcomes 10.3, 10.6, 10.7, 10.8]
File Needed: EX2019-IndependentProject-10-6.xlsx (Available from the Start File link.) and Text-BoydAir10.txt and Database-10.accdb (Available from the Resources link.)
Completed Project File Name: [your name]-EX2019-IndependentProject-10-6.xlsx
Skills Covered in This Project
Import a text file in Power Pivot.
Get a database table in Power Pivot.
Create a relationship in Power Pivot.
Create a PivotTable from the data model.
Format a PivotTable.
IMPORTANT : The projects in this chapter are for Windows (PC) computers only. The Power Query , Power Pivot , and Get & Transform features used in these projects are not currently available in Excel 365 for Mac.
Open the EX2019-IndependentProject-10-6 start file. Click the Enable Editing button. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. Launch Power Pivot . Import the Text-BoydAir10.txt file from your downloaded from the Resources link. Use the first row as column headers. Get the TblBoydAir table from the Microsoft Access Database-10 downloaded from the Resources link. NOTE: If you receive a Security Warning message, click OK and continue on to the next step. Create a relationship using the date field as the common field. Create a PivotTable from the Power Pivot data model in cell A1 in your existing worksheet. Name the worksheet PivotTable . Display the Flight and the FlightDate fields from TblBoydAir in the Rows area with the Flight field first. If the date field separates to include months or quarters, drag unnecessary field names out of the PivotTable Fields pane to remove them. Display the Passengers field in the Values area. Choose tabular form for the report layout. Place the Code field from the Text-BoydAir10 list. It is a Rows field. Insert a slicer for the Arrival field. Apply White, Pivot Style Medium 15 style to the PivotTable and White, Slicer Style Dark 3 to the slicer. Filter the data for GRB (Figure 10-86). Position the slicer in the top left corner of cell F2 as shown in the figure. Figure 10-86 Completed PivotTable for Excel 10-6 Save and close the workbook. Upload and save your project file. Submit project for grading.
Reset Progress Back to top
0 comments