Pinterest Pixel

Import Web Data Using Power Query

  Power Query lets you clean and transform your raw & messed up data into a format where you... read more

Download Excel Workbook
Bryan
Posted on

Overview

Import Web Data Using Power Query | MyExcelOnline

 

Power Query lets you clean and transform your raw & messed up data into a format where you can do further Excel analysis with ease.

Firstly, we would need data to play with Power Query, right?  The good thing with Power Query is that there is a multitude of ways to pull in data.

One ways is to get data from the internet by using the importing web data feature.  I will show you how we can parse a webpage and use it for Power Query!

Import Web Data Using Power Query | MyExcelOnline

download excel workbook Import-Web-Data-1.xlsx

Click to see tutorial on how to install Power Query in Excel 2013

Click to see tutorial on how to install Power Query in Excel 2010

In Excel 2016 it comes built in the Ribbon menu under the Data tab and within the Get & Transform group.

 

Let’s go through the steps in detail using Excel 2016 as our workbook:

STEP 1: Go to Data > New Query > From Other Sources > From Web. 

See also  How to Separate Date and Time in Excel

(In Excel 2010 & 2013 you need to go to Power Query > From Web)

Import Web Data Using Power Query

 

STEP 2: This will ask for a website where you want to get the data from.

We want to get the stock market indexes from Google finance.

Copy and paste this URL: https://www.google.com/finance

Press OK to continue

Import Web Data Using Power Query

 

STEP 3: Power Query will now try to parse this webpage, and in doing so, has determined that there are multiple tables of data in there.

Here is what Power Query brings back as Table options:

Import Web Data Using Power Query

By clicking on the Table options in the Navigator pane, you will get a preview of the web data on the right hand side.

Let’s select Table 0 for our example and press Edit.

Import Web Data Using Power Query

 

STEP 4: The data is now imported into the Query Editor window.

Import Web Data Using Power Query

 

STEP 5: We are going to transform Column3 so the data can be easier to analyze.

See also  Delete Steps Until End In Power Query

Select the Column3 heading with your mouse and go to Home > Split Column > By Delimiter.

Import Web Data Using Power Query

 

STEP 6: We want to split the third column into two separate values.  One for the actual number, and another for the percentage.

To achieve this, we need to split the data by the space in between the two values.

Select Space from the delimiter drop-down and press OK.

Import Web Data Using Power Query

 

STEP 7:  Our imported web data has been transformed into our liking.

Import Web Data Using Power Query

 

We are now ready to load it into our Excel worksheet by selecting Close & Load

Split First & Last Name Using Power Query

 

STEP 8:  Now that the data is in your Excel worksheet, tomorrow all you need to do is select the data & press the Refresh button from the Workbook Queries pane on the right hand side.

Import Web Data Using Power Query

 

You will have the latest stock prices & all this without copying and pasting from the webpage! Thank You Power Query 🙂

See also  Microsoft Excel Training | Free Excel Online Training Courses

 

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Import Web Data Using Power Query | MyExcelOnline
Import Web Data Using Power Query | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!