Challenge Project 10-8

For this project, you create queries for tables in a music database. You get data from Microsoft Access and build a dashboard with PivotTables.

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

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

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

Create and save a workbook as [your initials] Excel 10-8. Modify your workbook according to the following guidelines:

• Launch Power Query and get tblAlbums and tblSongs from the Challenge-10 Microsoft Access database in your student data files.
• Review the fields and data types in each query and determine the common field. Remove the Length field from one of the tables.
• Merge the queries into a new query and determine what type of join you need. Expand all fields and remove the AlbumID from either of the tables.
• Load the merged query to your workbook.
• Build a PivotTable that displays the album name, the artist, and the genre in tabular format. No totals are necessary. Name the PivotTable.
• Select and copy the PivotTable. Display the song ID, the song title, and the length. The Length field ungroups into days, hours, and minutes; keep the minutes field. Name the PivotTable.
• Move the PivotTables so that you can build a dashboard.
• Insert a slicer for the album name and connect it to both PivotTables.
• Design and layout your dashboard. Format the PivotTables and the slicer as desired.