Extract, transform and load relevant data

Trina's has already extracted the data and provided you with several files to help address its questions. The files and their contents are as follows:

• File:Analytics_mindset_case_studies_Trinas_Trinkets_sales.xlsxis a Microsoft Excel file that containsfivetables of output from Trina's sales system. Thefivetables are:
• SalesTransactions: contains a listing of the items ordered for each invoice, the quantity ordered and the price per item. Item prices can fluctuate based on the sale (for example, sometimes discounts are given) so there is not a uniform price for each item. Note that negative numbers in the quantity field indicate returns.
• Invoice: contains the date and the customer ID associated with the invoice. Each invoice is to asinglecustomer.
• Products: lists the 3,675 different products that the business sells. Each product has a unique stock code.
• Customer: lists the 4,363 customers who have done business withTrina's. The file lists the name of the key contact person (whether individual or business) and their zip code.Trina did not send you the address foreach customer or other information about theirbusinesses to protect this sensitive information.
• ZipCodes: A file that contains information about each zip code in the states in which Trina's operates.
• File:Analytics_mindset_case_studies_Trinas_Trinkets_tax_rates.csvlists the tax rate (gathered in December 2017)for each zip code in the states in which Trina's operates. The file lists the combined rate for the jurisdiction as well as the state tax rate, county rate, city rate and any additional special rates, if applicable.

Required

• Based on Trina's operations and state sales tax collection requirements, consider how the tax rate data might need tobe transformedconsidering origin-based or destination-based implications.
• Once you have completed this transformation, save your file asAnalytics_mindset_case_studies_Trinas_Trinkets_tax_rates_transformed.csv
• As the sales data is already loaded into an Excel file, you only need to add the transformed tax rate data to it. Load this data in as a separate worksheet tab namedTaxRatesCombined. Save the file asAnalytics_mindset_case_study_solutions_Trinas_Trinkets_Excel.xlsx.

Other cleansing and formatting of the datawill be donethroughout theremainder of the case as needed.