Shirt_Orders.accdb is an Access database file with three related tables: Customers, Orders, and Products. The goal here is to create a table in Excel that contains the of date, customer name, and product description for all orders that satisfy conditions on orders, products, and customers. Proceed as follows: a. Use Power Query to import all three tables into Excel as tables (not a Data Model). The only use for Query Editor is to remove the columns not used for this problem: Street, City, State, Zip, Phone for customers, Discount for orders, and UnitPrice for products. b. Add three new fields, Customer, Product, and Gender, to the Orders table in Excel and use VLOOKUP functions to fill them. c. Filter the Orders table as necessary. For this problem, use a " \( >75 \) " filter for units ordered, "Both" for gender, and both "Threads" and "Shirts R Us" for customer. What is the total number of orders for the selected filters?

Below is one acceptable solution that explains the process and arrives at the answer below. In our example we begin by importing the three tables (Customers, Orders, Products) from Shirt_Orders.accdb using Power Query (imported as regular Excel tables, not as a data model). In Query Editor we remove the “extra” fields: in Customers the fields Street, City, State, Zip, and Phone; in Orders the Discount field; and in Products the UnitPrice field. (After closing & loading, the tables are available on separate worksheets as flat tables.) Next we work with the Orders table. We add three additional columns:  • A Customer field (filled via a VLOOKUP into the Customers table to retrieve the customer name)  • A Product field (by VLOOKUP into the Products table)  • A Gender field (by VLOOKUP into the Customers table so that each order’s customer gender is known). Then we filter the Orders table on three conditions:  1. Units Ordered is greater than 75.  2. The Gender field equals “Both”.  3. The Customer field (that we filled via VLOOKUP) is either “Threads” or “Shirts R Us.” After applying the filters the remaining rows (orders) add up to a total of 4. Thus, the total number of orders for the selected filters is 4. Note: Although there are several ways to accomplish this (for example, by combining the queries or using Data Model relationships) the procedure described above is one acceptable method to obtain the correct answer of 4 orders.