Expert answer:Help with Excel homework
Answer & Explanation:Question requirements see attachmentA11 Power Query and Power View.pdf
a11_power_query_and_power_view.pdf
Unformatted Attachment Preview
Power Query and Power View
In this assignment we will look at how the BMI index “expand “over time and also over the GDP of
countries.
To begin we will need to download three data files from Gap Minder. The files can be obtained from
http://www.gapminder.org under the Data tab.
The first data file that you will need is the BMI data file.
Steps to obtain the data files:
1. Click on the DATA tab on the Gap Minder home page.
2. In the Search: box of the “Data in Gapminder World” page, type BMI.
3. You should see the following page with two entries.
4. Right click the Excel icon for the file you want to download (Pick the gender that correspond to
your gender) and click on Copy Link Location (this will copy the URL to the Clipboard).
5. Launch Excel (You should have Power Query installed already) and click on the Power Query
tab.
6. Click on the From Web icon (top left corner).
7. Hold the Ctrl key and press the V key on your keyboard to copy the URL that is in the
Clipboard to the textbox, then the click OK button in the From Web pop up Window.
8. In the Navigator pop-up Window, click on the Data table icon and a preview of the data set
appears on the right of the pop-up. Click on the Edit button to edit the data set.
9. In the Query Editor you want to use the first row as your header (Years from 1980 to 2008).
To do that, click on the “Use First Row As Headers” icon in the Transform group.
10. You will notice that the table looks like a pivoted table. You will need to unpivot the data.
11. Select all the year column (from 1980 to 2008) by select the first year (1980) then scroll to the
right (Year 2008), hold the Shift key down, the select the 2008 column. This will select all
columns from the first selection to the last. Right click any column heading and click Unpivot
Columns.
12. Change the column headings to the following “Country”, “Year”, “BMI”
13. Change the data type of the “Year” column to Whole Number and the “BMI” column to
Decimal Number.
14. In the Name box under Properties in Query settings, type BMI.
15. Click Close & Load on the top left corner of the Query Editor to load the table to your
spreadsheet.
16. Rename the worksheet tab to BMI from sheet #.
Next you will need to get two other data sets (GDP and Population) from Gap Minder.
Follow the above steps to transform the data set.
For the GDP data set you will need the set from the World Bank (third item from the top) in Gap minder.
For the Population data set you will need the set from the Various Sources (on the second page) in Gap
Minder with search term “Population”.
For the GDP and Population data set, be sure to change the first column to “Country” and the second
column to “Year” and the third to GDP” or “Population” and change the data type accordingly. If you
skip these steps you will not be able to merge the three data sets into one. Be sure to delete any
unwanted columns so that you only have data from 1980 to 2008 before you unpivot your data.
After you have transformed your three data set, it should look like the following:
If you did not follow the above steps you will not be able to merge the above worksheets.
Merge the work sheets.
After you have your loaded you three data sets you are ready to merge them.
1. Start with your GDP worksheet. Click on the Power Query tab then click on the Merge button.
2. In the Merge pop-up window select BMI worksheet in the second text box then select the
Country and the Year field from both the GDP and BMI worksheet and click on the Join Kind
drop down arrow and select the Inner(only matching rows), the click the OK button.
3. In the Query Editor after the merge click on the top right corner of the NewColumn and then
select the BMI check box, then click the OK button.
4. Change the NewColumn.BMI to BMI.
5. Next you will need to merge the Population to the current merge table. To do this, click on the
Merge Queries button
6. Select the columns from the appropriate tables and be sure to select the Inner (only matching
row) for the Join kind and click OK.
7. In the Query Editor after the merge click on the top right corner of the NewColumn.Population
and then select the Population check box, then click the OK button as in step 3 above.
8. Change the NewColumn.Population to Population. Click the Name box in the Query Settings to
change the Name to POP GDP BMI. Click Close & Load and you should have a worksheet in your
workbook. Rename this worksheet POP GDP BMI.
Power View
Now we are ready to view your data.
To do this follow the steps below.
1. Click on the POP GD BMI worksheet you created in the previous steps.
2. Click on the Insert tab, then click the Power View Icon
3. In the Power View Fields panel on the right, click on the drop down of the POP GDP BMI
table and drag the BMI, GDP and Country fields to the bottom section of the Power View
Fields panel.
4. Click the Design tab, then click the Other Charts icon, then click Scatter
5.
Enlarge the chart by dragging the left lower anchor.
6. Drag the population field to the Size box in the lower section of the Power View Fields panel.
7. Next drag the Year field to the Play Axis box in the lower section of the Power View Fields
panel.
8. To get a better view of your chart click on the filter icon in the chart.
9. To play the animation click on the play button icon (Year) at the lower left corner of the
chart.
10. Add a title to your chart and adjust the axis to get a better view of your animation.
11. Submit your worksheet 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