Expert answer:Need help with Excel homework
Answer & Explanation:A10 PowerQuery(1).pdf questions in ↑
a10_powerquery_1_.pdf
Unformatted Attachment Preview
Power Query
Self-Service ETL with Power Query
In this assignment we will use Power Query, an Excel add-on, to extract and transform data from two different
time period (years 2012 and 2013 – Hardware and OS Comparison of smartphones) into a table for further
analysis. To begin you need to download Power Query from here.
System Requirements
Supported Operating System
Windows 7, Windows 8, Windows Server 2008 R2, Windows Server 2012
o
Windows Server 2008 R2
o
Windows Server 2012
o
Windows 7
o
Windows 8
o
Windows 8.1
The following Office versions are supported:
o
Microsoft Office 2010 Professional Plus with Software Assurance
o
Microsoft Office 2013:
Power Query Premium: All Power Query features available for: Professional Plus, Office
365 ProPlus or Excel 2013 Standalone
Power Query Public: Available for all other Office 2013 Desktop SKUs. Includes all Power
Query features, except the following ones: Corporate Power BI Data Catalog,
Azure-based data sources, Active Directory, HDFS, SharePoint Lists, Oracle, DB2, MySQL,
PostgreSQL, Sybase, Teradata, Exchange, Dynamics CRM, SAP BusinessObjects,
Salesforce.
Microsoft Power Query for Excel requires Internet Explorer 9 or greater.
Microsoft Power Query for Excel is available for 32-bit (x86) and 64-bit (x64) platforms, your selection
must match architecture of the installed version of Office.
Once you have downloaded the version for your system, follow the steps below to extract and transform data
from Wikipedia.
1.
Open a new blank Excel spreadsheet and go to the Power Query tab.
2.
3.
In the Power Query tab, click on the data Catalog icon on the ribbon of your spreadsheet (see above figure),
This option allows you to browse public data i.e. the Internet.
On the right-hand you will see the Online Search pane. In the search box, type smartphone 2013 and press
Enter.
4. Hover your cursor over the 2013 – Hardware and OS – Comparison of smartphones link,
You should see a preview of the data to the left (see below figure). These instructions are based on the
datasheet shown here. It may not work if you pick the incorrect data sheet.
5. Click on the Load link at the bottom left of the preview pane.
After a few seconds, the data will be imported into your spreadsheet.
Rename the sheet “2013 Release” and delete the extra sheet (sheet1)
6. From the Power Query Tab select Launch Editor.
The Query Editor window will open. In this window, you will see a data table with columns and data rows.
Scroll to the right to find the Weight column.
We want to change this column to numeric type for analysis. Currently the data in this column is text data and
has a “g” character, which represents grams. We want to change the data type of this column to numeric and
remove the ”g” character.
7. To remove the “g” and change the data type from text to numeric data, do the following.
Right-click on the Weight column. You will see the menu option that will allow you to change (transformation) the
column properties. Click on the Replace Values… option (see figure below).
In the Replace Values dialog box, set “Value to Find” to “g” (without the parentheses) and leave the “Replace
With” box empty.
8.
After you click the OK button to replace all the “g” character in the column, you then need to change the data
type from text to “whole number” To do that right-click on the Weight column again, and under Change Type,
choose Whole Number.
9.
We do not need all columns in the table so these unwanted columns will be removed from our table.
We will only need the following 5 columns for our analysis; Model, OS, Weight, Other and Key. To remove the
other columns do the following:
Click on the columns that we will keep i.e. Model, OS, Weight, Other and Key. While holding down the Ctrl key
on your keyboard (to select multiple columns).Then, right-click on one of the column headers and select
Remove Other Columns (Be sure you are selecting the columns that we do not need).
10. Drag-and-drop the Key column (the last column of your table) from the end to the beginning of the dataset.
The dataset will change as shown in the following.
11. Right-click on the “Other” column, and under the Split Column option, choose By Delimiter…, as shown in the
screenshot below.
In the split column by delimiter dialog box, leave the delimiter as comma with default options. Click OK.
12. After the split, you will see 21 columns with headers such as Other.1, Other.2 and so on.
You will see that the separate columns look like pivoted information. Select all Other.X columns together by
selecting the Other.1 then holding down the Shift key on your keyboard select all Other column including the
Other.21 column. This will select all the “Other.X” column (Be sure you select all the Columns name Other.X)
Right-click on the column header and select Unpivot Columns from the pop-up menu (see figure below).
13. After Unpivot, you will see two columns, namely Attribute and Value.
14. We want to add a new column to identify the year that the phones were release. To do that Click on the
small table icon in the header of the column’s header row and then choose Add Custom Column…, as shown in
the screenshot below.
15. In the New column name box, type Release Year.
In the Custom column formula Box type 2013 after the = sign and click OK.
You will see that the new column has appeared at the end of all the columns.
16. Click on the “Close and Load” button to load the dataset to the Excel spreadsheet.
You should see a spread sheet that looks like the following:
The next step is to get the data set for the previous year (2012).
17. Go to the POWER QUERY tab again and then click on Online Search. Type Smartphones 2012, choose “2012 –
Hardware and OS – Comparison of Smartphones” and add it to the worksheet.
Rename the sheet “2012 Release”.
18. You will need to go through the same transformations on this dataset like the previous data set.
Repeat step 1 through step 16 above for the 2012 dataset, except that the Release Year for this dataset is
2012.
19. After loading the 2012 transformed data into the Excel spreadsheet, rename the worksheet to “2012 Release”
Next step is to merge the two worksheet.
In the POWER QUERY menu, click on the Append menu and append the 2012 to the 2013 dataset.
In the Append window select the 2012 data set from the drop down list as the primary table and the 2013
table as table to append with the primary table.
20. You will see two datasets combined in the Query Editor window. Right-click on the Value column and remove
the Value column.
Select the Model header and in the Reduce Rows group click on the Remove Duplicates, as shown in the
screenshot.
21. Remove Duplicates option will remove records that have the same values in all columns for two separates
rows. After removing duplicates, you will have a single record for each smartphone, as shown in the
screenshot.
Click on the Close & Load to save to worksheet.
worksheet as “2012 – 2013 “
Be sure to name all worksheets.
Name the merged
You are done!! You had just extracted and transformed some data for further analysis.
Save and submit your workbook to Blackboard.
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades