THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

BI Beginner: Use Power Query To Get Data From Web Pages

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.

image

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.

image

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.

image

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.

image

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.

image

Figure 5. Editing the imported table.

Click Apply & Close to close the Query Editor and complete the import process.

image

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.

Published Thursday, March 13, 2014 10:39 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement