Independent Project 10-5

The New York Department of Motor Vehicles tracks information about citations, drivers, and officers in separate tables. You import the tables in Excel and in Power Query to build PivotTables with a slicer and a timeline.

[Student Learning Outcomes 10.1, 10.2, 10.4, 10.5, 10.6, 10.7]

File Needed: Database-10.accdb (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] Excel 10-5.xlsx

Skills Covered in This Project

• Get data from an Access database in a workbook.
• Get external data in Power Query.
• Transform data in in Power Query.
• Build PivotTables from the data model.
• Design a dashboard with PivotTables.
• Insert a slicer for PivotTables.
• Insert a timeline for PivotTables.

1. Create a new workbook and save it as [your initials] Excel 10-5.
2. Get data from an Access database and in Power Query.
   1. Click the Get Data button [Data tab, Get & Transform Data group] and load tblDMVDrivers-10 from Database-10 directly to your workbook. It displays on a sheet with the same name as the Access table. (Rename the sheet if necessary as tblDMVDrivers-10.)
   2. Launch Power Query. The Excel table is part of the data model.
   3. Click the New Source button [Home tab, New Query group] and choose Database and Access.
   4. Import tblDMVOfficer-10 from Database-10 in your student data files and then import tblDMVTickets-10. The data model has three tables or queries.
3. Transform data in the queries.
   1. Remove the Gender field from tblDMVDrivers-10.
   2. Edit the label for the first column (license number) to DLN and change the data type to Whole Number.
   3. Edit the label for the second column to DOB and change the data type to Date.
   4. Edit labels to display LName and FName.
   5. Change the Zip field to Whole Number.
   6. Display the tblDMVOfficer-10 query and edit the label in the first column (officer ID) to Badge.
   7. Edit the name labels to display First and Last.
   8. Edit the label for the last column to DOH and change the data type to Date.
   9. Display the tblDMVTickets-10 query.
   10. Change the first label to Ticket and the second label to DLN.
   11. Change both data types to Whole Number.
   12. Change the label in the third column to Date and set the data type to Date.
   13. Display Offense and Badge as labels.
4. Close and load the queries to your workbook. The tblDMVDrivers table is refreshed and the other two tables are newly loaded. Rename the sheets to match the query names if necessary (see the Queries & Connections pane for query names).
5. Summarize data in a PivotTable.
   1. Select a cell in the tickets table.
   2. Click the Summarize with PivotTable button [Table Design, Tools group], place the PivotTable on a new worksheet, and add the data to the data model.
   3. Display the ticket and the offense as Rows. If Sum of Ticket displays in the Values area, drag it to the Rows area below the Offense field.
   4. Display the fine as a Values field.
   5. Choose tabular form for the report layout and turn off subtotals if they display.
   6. Format the values in column C as Currency with no decimal places and change the label to Fine.
6. Create a relationship for a PivotTable.
   1. Click All in the PivotTable Fields pane and expand the tblDMVDrivers-10 list.
   2. Place the DLN field in the Rows area as the third entry.
   3. Detect or define the relationship between the tickets and the drivers tables using the DLN field.
   4. Turn off the display of grand totals.
   5. AutoFit each column.
   6. Name the PivotTable as pvtTickets.
7. Copy PivotTables.
   1. Insert a column at column A. You will use column A later in the project for the slicer.
   2. Select the entire PivotTable and copy and paste it in cell G3. Values display in exponential form when the column is too narrow; AutoFit the columns to see the values.
   3. Name the PivotTable in cell G3 as pvtOfficers.
   4. Remove all fields from the PivotTable.
   5. Place all fields from tblDMVOfficer-10 in the Rows area. If the date field separates into three fields for year, quarter, and month, remove the sectioned fields leaving only the DOH field and drag DOH to the Rows area as the last field.
   6. AutoFit the columns as needed.
   7. Apply the None style from the PivotTable Styles gallery. It is the first icon (Figure 10-96).
   8. Select and copy pvtOfficers to cell L3 and name the new copy pvtOffenses.
   9. Remove all fields and display the Offense field in the Rows area and the Fine in the Values area.
   10. Display grand totals for the columns. AutoFit the columns.
   11. Format the values as Currency with no decimals.
   12. Edit the label to display Fine.
8. Layout the dashboard.
   1. Name the worksheet as Dashboard.
   2. Set the width of column A to 30.
   3. Select pvtTickets, the first PivotTable, and apply the None style to match the other tables.
   4. Insert a slicer for the officer’s last name.
   5. Position the slicer in the middle of column A with its top border at row 3 and size it to display all names.
   6. Connect the slicer to the pvtTickets and pvtOffenses tables.
   7. Apply White, Slicer Style Light 3.
9. Insert and connect a timeline.
   1. Select row 2 and set the height to 132.
   2. Select pvtTickets, the first PivotTable.
   3. Insert a timeline for the ticket date field.
   4. Display Months as the time division.
   5. Position the timeline in the middle of row 2 with its left edge at column B.
   6. Apply Light Gray, Timeline Style Light 3.
   7. Connect the timeline to pvtOffenses in addition to the tickets table.
   8. Set the column width to 5 for columns F and K.
   9. Hide the gridlines.
10. Add a second relationship for the PivotTables.
    1. Click a cell in the pvtTickets table.
    2. Click the Relationships button [PivotTable Analyze tab, Calculations group]. One relationship between the tickets and the drivers tables displays.
    3. Click New.
    4. Create a relationship with tblDMVTickets as the Table and tblDMVOfficer as the related table and determine the common field.
11. Select Torres and Markley in the slicer and August 2021 in the timeline.
12. Save and close the workbook (Figure 10-97).