Please write out detailed steps to complete this project, these steps should include where to access each tool. Thank you. Project Description:

Pansy Berger has a small sports equipment store that has a growing list of customers. The different supervisors have been saving customer information in different formats. Pansy would like to have all the data in an Excel spreadsheet. Pansy has asked you to take on the task of getting the data together and putting them in the formats for the planned marketing effort.

Steps to Perform:

Step Instructions Points Possible

$1$ Start Excel. Open the downloaded workbook named Excel$\_$Ch$09\_$PS$1\_$List.xlsx$.$ Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files. $0$

$2$ Data has been provided in a CSV file. In order to use the data for further analysis or communication with customers, it first needs to be connected to Excel.

Using Get & Transform, establish a connection to the Excel$\_$Ch$09\_$PS$1\_$Mailing.csv file. Before the mailing can go to customers, the data within the database needs to be transformed into more usable formats. $12$

$3$ First, delete the ID column from the query as it will not be used to contact customers. Next, the names provided in the database contain extra spaces that need to be removed before the data can be used. Some of the names are also in lower case formats and need converted to proper case.

Use the Trim transformation in the Power Query Editor on the Transform tab to remove extra spaces from the Full Name column. Use the Capitalize Each Word transformation to proper case the names in the Full Name column. $20$

$4$ In order to be more useful in a marketing effort, the names of the customers need to be split into first and last name columns.

Separate the names in the Full Name column into new columns named First Name and Last Name. Delete the Full Name column. Remove any extra spaces that may have been created during this process. Move the First Name column to the left of the Last Name column. $12$

$5$ Using the Date of Birth field you can determine the age of the customers.

On the Add Column tab and in the Date & Time group, use the Date transformation to create a new column displaying the Age of the customer. On the Transform tab, in the From Date & Time group, use the Duration transformation to display the Age as total years. On the Transform tab, in the Number Column group, use the Rounding transformation to round down the Age to the nearest integer. Move the Age column to the left of the Address column. $6$

$6$ In order to work more easily with the addresses, separate columns need to be generated for the street address, city, and state data.

On the Add Column tab and in the General group, use the Column From Examples transformation to separate the street address from the Address column. For example, $4867$ Nonummy Ave, Provo UT should have a Street Address of $4867$ Nonummy Ave. Rename the new column to Street Address. $16$

$7$ On the Add Column tab and in the General group, use the Column From Examples transformation to separate the city from the Address column. For example, $4867$ Nonummy Ave, Provo UT should have a city of Provo. Rename the new column City. $16$

$8$ On the Add Column tab and in the General group, use the Column From Examples transformation to separate the state from the Address column. For example, $4867$ Nonummy Ave, Provo UT should have a state of UT$.$ Rename the new column State. $16$

$9$ Delete the Address column and arrange the renamed columns in the following order: First Name, Last Name, Date of Birth, Age, Street Address, City, State. Load to the Mailing List worksheet in cell A$2.2$

$10$ Save and close Excel$\_$Ch$09\_$PS$1\_$SportsCustomers.xlsx$.$ Exit Excel. Submit your files as directed. $0$

Total Points $100$AutoSave On $1$ Excel$\_$Ch$09\_$PS$1\_$List$\_$solution.xlsx $-$ Last Modified: June $29*$

Your Office