- Power BI – Excel, Power Query, Power View
- R
With Power Query you can:
Download and install Power Query
- Download the Power Query for Excel add-in.
- Open the folder where you downloaded Microsoft Power Query for Excel.
- Double-click the downloaded installer file, and then follow the steps in the wizard.
- After the installation is complete, click Finish.
- Power Query will appear as a new tab in the Excel ribbon.
- Run the MSI installer and follow the setup steps.
Enabling Power Query
- Once Power Query is installed, you may need to enable it. To do this, go to File and then choose Options. The Excel Options dialogue box will open.
- Go to the Add-Ins option on the left hand side.
- At the foot of the Excel Options box, you will see an option to Manage options. In the drop down list, look for COM Add-ins.
- Make sure that Power Query is checked.
Connecting to External Data Sources
Connecting to Web Page data
- To open Internet Explorer, press the Windows ( ) button.
- Then, in Start, commence typing Internet Explorer.
- When the Internet Explorer application appears, click the application tile.
- Go to the Wikipedia Human Development Index page in order to see it before we import it.
- We will now import the data to this page.
Add a Wikipedia page data source to Power Query
- In the POWER QUERY ribbon tab, click From Web.
- In the Web Content dialog box, in the URL text box, paste the Wikipedia URL (http://en.wikipedia.org/wiki/List_of_countries_by_Human_Development_Index).
- Click OK.
- In the right hand side, you will see a list of Tables. Click Table 2, and you will get a preview of the data.
- Double click on it, and you will see the data appear in the Excel window.
- Click on it, and rename the query to HDI Very High.
- Make sure to click on Add to the Data Model at the bottom right hand side.
- Next, click on Apply and Close.
- You will be returned to the Excel workbook.
- In the drop-down list, you will see a list of your recent sources. Select the Wikipedia page.
- You will be returned to the Power Query Editor, and you will see the tables on the right hand side.
- Select Table 3 by double-clicking it. Next, Make sure you have clicked on Add to the Data Model at the bottom right hand side.
- Repeat these steps for the medium Human Development information.
- For the Medium and Low Human Development Indexes, you will need to repeat these steps using Table 4 and Table 5 respectively.
- To do this, highlight the column Rank Change in rank between 2013 report to 2011 report[1] and right-click to delete it. We do not need it so if we select Remove, the column will be deleted.
- Click on Row number 1. We will make this row the header row. To do this, select Use First Row as Headers and you will see that this row is now the header row.
- Rename the HDI column to HDI 2012 by right clicking it, and selecting Rename.
- We will still have a row that is text. To remove it, highlight the first row and select the button Remove top Rows from the Home tab in the Power Query Editor.
- Change the HDI column to a Decimal Number by highlighting the column, going to Data Type and selecting Decimal Number from the drop down list.
- Let’s rename the HDI_1 column to Difference by right-clicking it and selecting Rename.
- Let’s add in a custom column. To do this, right click on the Difference column and select Insert Custom Column
- In the Insert Custom Column editor, double click the HDI 2012 column to put it into the text box.
- Then, enter a minus sign after the HDI 2012 column
- Finally, double click the Difference column to select it and put it into the text box.
- Your query should look like this:
- [HDI 2012] – [Difference]
- Press the return button. Your final query output should appear as follows.
- Now click Apply and Close.
- Rank
- Country
- HDI 2012
- Difference
- HDI 2010
- To do this, select the HDI 2010, HDI 2012 and the Difference columns.
- Go to the Transform tab and go to the option Unpivot Columns.
- You will now see that the HDI 2010, HDI 2012 and the Difference columns would go in the same column, which is headed Value.
- The attribute names have gone into a column called Attribute.
- The other two columns, Rank and Country, have stayed the same.
- We don’t actually need the Difference column now that we have obtained the data, so let’s filter it out.
- To do this, go to the Attribute column heading and click the downward arrow next to it.
- Deselect the Select All button, and select only the HDI 2010 and HDI 2012 options, and then click OK.
- Go to the option Replace Values and the Replace Values box will appear.
- In the Value to Find box, enter HDI 2012.
- In the Replace With box, enter 2012 and then click OK.
- Repeat these steps for HDI 2010, renaming it to 2010.
- Let’s rename the Attribute column to year. To do this, select the column and then right click. In the pop up menu that appears, select Rename. Rename the column to Year.
- Now, let’s rename the Value column so that it is now called HDI Value. To do this, select the column and right click to get the pop up menu again. Look for the rename option, and rename the column to HDI Value.
- If we examine the Rank column, we can see that some countries and jointly ranked. So, for example, Hong Kong and Iceland are jointly ranked 13th top in the list.
- Let’s add an index to our Power Query. We could use this later as a key in the table.
- To do this, right click on HDI Data and click Edit.
- The Power Query Editor will appear again. Go to the Add Column tab in the Power Query Editor and select the option Insert Index Column.
- The Index column will appear at the right hand side. To move it, select the column and go to the Move option.
- Look for the option ‘To the Beginning’ and select it. The Index column will move to the left hand side of the table.
- Once you have done this, go to the Home Tab and click on the Apply and Close button.
Connecting to Online data
- Click on the Power Query tab.
- Click on the Online Search button.
- The Online Search box will appear on the right hand side.
- Type in past and future GDP in the search box and click return.
- The search box will show two tabs: one is organisation and the other is public. If you click on organisation, you will probably see no data. If you click on public, there will be many data sources to choose from.
- This will take you to a Terms and Conditions page. After you’ve read the Terms and Conditions, and, if you agree with them, tick the box to specify that you agree, and click on Sign Up.
- This will take you to a Thank You page. Look for the link to Explore this Dataset on this page, and click it.
Really gold and a great follow up to the last of the five R webinars, I really appreciate you posting it.