We’ve all seen data on a web page and wished we had it in Excel. Copying a table from a web page and pasting it into Excel often leads to disappointing results. Power Query makes it easy to import data from a web page into Excel. As a nurse, I’m particularly interested in analyzing public health data for research purposes. I found some good data on tuberculosis treatment success rates at the World Bank’s website. You can find the data here. The screen captures in this post were made on a Windows 8.1 desktop with Office 2013. Power Query was downloaded from here.
Figure 1. World Bank tuberculosis data.
On the POWER QUERY tab in Excel, click From Web to bring up a dialog box for entering the web page’s URL. Enter the URL for your web page and click OK.
Figure 2. Enter the web page URL into the Power Query dialog box and click OK.
After a few seconds, Excel presents the DOM in the Power Query Navigator. Use the Peek feature to examines the contents of the items in the Navigator list.
Figure 3. Mouseover on Table 0 invokes the Peek feature to show a preview of the table on the World Bank’s web page.
Double-clicking Table 0 in the Navigator brings up the Query Editor.
Figure 4. Table imported from web page ready for editing.
Edit the data as needed before completing the import process. I deleted the two rightmost columns and named the table TB Treatment Success Rates.
Figure 5. Editing the imported table.
Click Apply & Close to close the Query Editor and complete the import process.
Figure 6. Data imported from World Bank web page table into Excel.
Power Query is a very useful addition to Excel that makes it easy to conveniently and quickly bring data from just about any data source into Excel. I find it a valuable tool to use both at work and at school.