Excel 2021 In Practice - Ch 10 Independent Project 10-6 (Windows Only) - Full answer in 2025

Independent Project 10-6
These instructions are only compatible with the Microsoft Windows operating system.
Steps to complete This Project
Mark the steps as checked when you complete them.
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]
File Needed: EX2021-IndependentProject-10-6.xlsx (Available from the Start File link.) and txtBoydLogistics-10.txt, and
Database-10.accdb (Available from the Resources link.)
Completed Project File Name: [your name]-EX2021-IndependentProject-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. Open the EX2021-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.
2. Get a text file and a database file in Power Query.
a. Launch Power Query.
b.
c.
Click the New Source button [Home tab, New Query group] and import txtBoydLogistics-10 from your
student data files.
Get tblBoyd-10 from the Microsoft Access Database-10 file in your student data files.
3. Transform data and merge queries.
a.
b.
c.
d.
e.
4.
Select the Date field in tblBoyd and set the data type to Date.
Merge the queries as a new query including all records from tblBoyd with matching rows from
txtBoydLogistics. Identify the common field.
Expand fields from txtBoydLogistics-10 in the merge query and remove its Code field.
Close and load the queries.
Rename each sheet if necessary to match the query name. Confirm query names in the Queries &
Connections pane.
Create a PivotTable for the Merge1 range on a new worksheet. Name the worksheet PivotTable.
5. Design the PivotTable.
a. 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.
b.
c.
d.
e.
f.
g.
h.
i.
Display the Pallets and Miles fields in the Values area.
Choose tabular form for the report layout and do not show subtotals.
Right-click a date and choose Ungroup if the full date does not display.
Edit the labels in cells D3 and E3 to # of Pallets and # of Miles.
Format the value fields in columns D:E as Number with a comma separator and no decimals.
Apply Light Gray, Pivot Style Light 15 style to the PivotTable.
Select the entire PivotTable and apply All Borders.
AutoFit the columns.
6. Insert and format a slicer.
a.
b.
c.
d.
e.
Insert a slicer for the Destination field.
Apply White, Slicer Style Dark 3 to the slicer.
Change the caption for the slicer to Destination.
Position the slicer in the top-left corner of cell G2 and size it to show all city names.
Filter the data for Milwaukee.
7. Group rows in Power Query.
a.
b.
c.
d.
e.
f.
g.
h.
i.
j.
8.
Relaunch Power Query for the Merge1 query.
Duplicate the Merge1 query and name the copy as GroupBy.
Group the rows by Origin to sum the miles in a new column named Total Miles.
Close and load the queries. The new sheet displays and existing queries refresh.
Name the sheet GroupBy if necessary.
Show the Total row in the table and format the values as Comma Style with no decimals.
Edit the label in cell A1 to Origin.
Apply White, Table Style Light 15.
Select the table and copy it to cell K2 on the PivotTable sheet.
AutoFit the columns.
Save and close the workbook (
Figure 10-98).
Figure 10-98 Excel 10-6 completed
9.
10.
Upload and save your project file.
Submit file for grading

#MicrosoftExcel
#IndependentProject
#Query
#SIMNet
#Microsoft