Question: 1. Import the data from the Excel file (Lab 6 Industry Report.xlsx) by creating a new Query. To do this, use the From File /

1. Import the data from the Excel file (Lab 6 Industry Report.xlsx) by creating a new Query. To do this, use the From File / From Workbook option.

2. Edit the query and perform the following steps:

- Name the Query IndustryReport.

- Remove the first and second rows of the table.

- Promote the row that represents the title of the column to the table headers.

- Fill the Category column with the correct categories.

- Remove all the rows that represent total values.

- Remove the remaining rows that do not represent data.

- Remove the column that represent total values.

- Remove the remaining columns that do not represent data.

- Transform the monthly columns to rows and name the resulting columns appropriately. (Hint: You might find the Unpivot Columns feature useful).

- Rename the resulting columns appropriately.

3. Load the data into Excel data model.

Answer the following questions by reviewing the query you have just created.

a) How many rows were loaded into the data model?

b) How many columns were left in the resulting table?

The Excel file (Lab 6 Industry Report.xlsx) can be found at https://drive.google.com/open?id=0B04L250qBftHMzBFam5ZVkZ5OU0

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

Calculating by using queries 21? Updating records by using queries As you use a database and as it grows, you might discover that errors creep in or that some information becomes out of date. You can...

* Deliverable: One MS Access le only. Ensure that you submit only one database which will contain all the required database objects. * For Each database object you create (e. g., table, query, or...

haa23684_PlugInT6CD.qxd 9/6/06 5:38 PM Page 2 CONFIRMING PAGES P L U G - I N T6 Basic Skills and Tools Using Access LEARNING OUTCOMES 1. Describe the primary functions using Microsoft Access. 2....

1. What is the recommended way to create a new PivotTable style that is close to an existing style? Select an answer: I Duplicate the existing style and then Modify the new style. Apply the existing...

Version - Applied Analytics Part 4 Step 1: Import the data Connect to the Excel file. "Business Analytics Tableau Lab Quiz". There is just one worksheet, "invoices Tables should automatically import...

I need your help! Please help me! Total Students Average Rate Total Due TotalSessions FirstName Barrett Wang Daphne Irma Karen Leroy Ursa Ella Jena Zenia Stone Samantha Brenna Hiram Rate 15 10 1 7 1...

15. Hwan needs to identify the location of each project. Add a slicer to the PivotTable as follows: a. Add a slicer based on the State field. b. Move and resize the slicer so it covers the range...

Hi! I do not understand the beginning of this assignment at all. If someone could just answer question 2 and 3, I would greatly appreciate it! AutoSave OFF W...

The Business Case Renowned physician, Dr. May Kwell, is considering purchasing the medical practice of the only other physician in the city with her specialty, who has announced his retirement. She...

please help thank you Your audit firm, Maisy LLP, recently gained a new public client. The partner has assigned your team to do the fieldwork for this audit and has asked you to do a preliminary risk...

An investor in real estate may achieve a higher rate of return by acquiring a small portion (part ownership) of several properties, rather than a lesser number of whole properties. Explain this.

Cobalt(II) sulfate heptahydrate has pink-colored crystals. When heated carefully, it produces cobalt(II) sulfate monohydrate, which has red crystals. What are the formulas of these hydrates? If 3.548...

Interest earned on annuities is: taxed at half of your income tax rate. taxed at your income tax rate. only taxed to specific beneficiaries. tax free.

Wrought iron does not have Uniform strength in all directions Ability to hold protective coating High ductility & corrosion resistance Easily weldable characteristics

Suppose government spending increases. Would the effect on aggregate demand be larger if the Federal Reserve took no action in response or if the Fed were committed to maintaining a fixed interest...

Suppose that people suddenly wanted to hold more money balances. a. What would be the effect of this change on the economy if the Federal Reserve followed a rule of increasing the money supply by 3...

Suppose the federal government cuts taxes and increases spending, raising the budget deficit to 12 percent of GDP. If nominal GDP is rising 5 percent per year, are such budget deficits sustainable...