Help me solve and work through this by giving me detailed step by step descriptions with the locations of where to access each tool. 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\_$Assessment$\_$SportsCustomers.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 Microsoft Access database. 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$3-$ SportsCustomers.accdb database. Before the marketing plan can be implemented, the data within the database needs to be transformed into more usable formats. $8$

$3$ The names provided in the database contain extra spaces and non$-$printable characters that need to be removed before the data can be used. Some of the names are also in lower case formats and need to be converted to proper case.

Use the Clean and Trim transformations in the Power Query Editor on the Transform tab to remove extra spaces and non$-$printable characters from the Customer name column. Use the Capitalize Each Word transformation to proper case the names in the Customer 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 Customer name column into new columns named First Name and Last Name. If necessary, delete the Customer name column. Remove any extra spaces that may have been created during this process. Sort the data by First Name. X

AutoSave

On

$q,$

$$

Excel$\_$Ch$09\_$PS$3\_$SportsCustomers$\_$solution.xlsx $-$ Last Modified: June $29-$

Search $($Alt $+$

B$4$

$fx$

BerryProject 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:

$5$ 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, $6813$ Ligula Av$.,$ Santa Fe NM should have a street address of $6813$ Ligula Av$.$ Rename the new column Street Address.

$6$ 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, $6813$ Ligula Av$.,$ Santa Fe NM should have a city of Santa Fe$.$ Rename the new column City.

$7$ 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, $6813$ Ligula Av$.,$ Santa Fe NM should have a state of NM$.$ Rename the new column State.

$8$ Delete the Address column and arrange the renamed columns in the following order: First Name, Last Name, Street Address, City, State. Load the CustomerDatabase worksheet in cell A$2.2$

$9$ Save and close Excel$\_$Ch$09\_$Assessment$\_$SportsCustomers.xlsx$.$ Exit Excel. Submit the file as directed.