Power Query: Pull data from a website into Excel

For this tutorial, you will need to have Power Query installed. If you are running Office 2016, Power Query should already be available. For Excel 2010 and 2013, here is a link to the download: Power Query

Power Query makes pulling data from a website quick and easy. In this example we will be extracting data from the Wikipedia page “List of NCAA Men’s Division I Basketball champions“. Here is a link to the website: Link to Wikipedia Site

If you open up the Wikipedia page and scroll down a bit, you will see a table:  Championship games, by year, showing winners and losers, final scores and venues. This the table we will be extracting into Excel.

powerQuery-web.jpg

  1. Open up a new Excel workbook. Locate the Power Query Tab on the Ribbon Bar
  2. Select Web Page
  3. In the pop up window, copy and paste the URL to the Wikipedia Page
  4.  Click Ok

powerQuery-web1

5. Notice as you click through the list of tables on the left side of the screen, the tables             appear in the preview screen on the right.

6. Select Championship games, by year….

7. Click Load 

powerQuery-web2.jpg

Congratulations. You now have the table from the Web in an Excel sheet.

powerQuery-web3

 

 

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s