Challenge Project 10-9

For this project, you get data from a Microsoft Access database for real estate listings. You use Power Pivot to build the data model and create a dashboard.

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-9.xlsx

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

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

• Launch Power Pivot and get tblListings from the Challenge-10 Microsoft Access database in your student data files.
• Create a PivotTable from the data model on the existing worksheet. Show the listing ID, the city, the square footage, and the price as Rows fields in a tabular layout. Name the PivotTable.
• Format the square footage amounts to show a comma, no decimals.
• Close the Power Pivot window.
• Get tblAgents from the Challenge-10 Microsoft Access database in your student data files and load it to a new sheet in the workbook.
• Launch Power Query and add a column from an example to display the first name, a space, and the last name. Name the new column Agent Name and delete the two source columns.
• Change the DateHired field to a Date data type and close and load the query.
• Insert a column at column E in the table. Type the first phone number in cell E2 in standard U.S. style (xxx) xxx-xxxx which includes a space after the right parenthesis. Select cell E2 and choose Flash Fill from the Fill button [Home tab]. Select and delete column D.
• Complete labels, formatting, and other edits as needed.