Independent Project 10-6

These instructions are only compatible with the Microsoft Windows operating system.

Boyd Logistics combines a text file with route information and a Microsoft Access table with transaction details. You create and merge queries to build a PivotTable.

[Student Learning Outcomes 10.1, 10.2, 10.3, 10.4, 10.6]

 DO NOT use the files below if you are completing an autograded SIMnet Project. Using the linked files below will prevent you from uploading and submitting your work in SIMnet. The files for all autograded SIMnet Projects MUST be downloaded from your class assignment.

Files Needed: txtBoydLogistics-10.txt and Database-10.accdb (IP-E10-6) (Student data files are available in the Library of your SIMnet account.)

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

Skills Covered in This Project

• Import a text file in Power Query.
• Get a database table in Power Query.
• Merge queries as a new query.
• Create a PivotTable from the merged query.
• Format a PivotTable.
• Insert and format a slicer.
• Group rows in Power Query.

1. Create a new workbook and save it as [your initials] Excel 10-6.
2. Get a text file and a database file in Power Query.
   1. Launch Power Query.
   2. Click the New Source button [Home tab, New Query group] and import txtBoydLogistics-10 from your student data files.
   3. Get tblBoyd-10 from the Microsoft Access Database-10 file in your student data files.
3. Transform data and merge queries.
   1. Select the Date field in tblBoyd and set the data type to Date.
   2. Merge the queries as a new query including all records from tblBoyd with matching rows from txtBoydLogistics. Identify the common field.
   3. Expand fields from txtBoydLogistics-10 in the merge query and remove its Code field.
   4. Close and load the queries.
   5. Rename each sheet if necessary to match the query name. Confirm query names in the Queries & Connections pane.
4. Create a PivotTable for the Merge1 range on a new worksheet. Name the worksheet PivotTable.
5. Design the PivotTable.
   1. Display the Transaction, Date, and Contractor fields in the Rows area in that order. If the date field separates into months or quarters, drag unnecessary field names out of the PivotTable Fields pane to only show Date.
   2. Display the Pallets and Miles fields in the Values area.
   3. Choose tabular form for the report layout and do not show subtotals.
   4. Right-click a date and choose Ungroup if the full date does not display.
   5. Edit the labels in cells D3 and E3 to # of Pallets and # of Miles.
   6. Format the value fields in columns D:E as Number with a comma separator and no decimals.
   7. Apply Light Gray, Pivot Style Light 15 style to the PivotTable.
   8. Select the entire PivotTable and apply All Borders.
   9. AutoFit the columns.
6. Insert and format a slicer.
   1. Insert a slicer for the Destination field.
   2. Apply White, Slicer Style Dark 3 to the slicer.
   3. Change the caption for the slicer to Destination.
   4. Position the slicer in the top-left corner of cell G2 and size it to show all city names.
   5. Filter the data for Milwaukee.
7. Group rows in Power Query.
   1. Relaunch Power Query for the Merge1 query.
   2. Duplicate the Merge1 query and name the copy as GroupBy.
   3. Group the rows by Origin to sum the miles in a new column named Total Miles.
   4. Close and load the queries. The new sheet displays and existing queries refresh.
   5. Name the sheet GroupBy if necessary.
   6. Show the Total row in the table and format the values as Comma Style with no decimals.
   7. Edit the label in cell A1 to Origin.
   8. Apply White, Table Style Light 15.
   9. Select the table and copy it to cell K2 on the PivotTable sheet.
   10. AutoFit the columns.
8. Save and close the workbook (Figure 10-98).