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!

DOWNLOAD EXCEL WORKBOOK

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. 

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

import-web-data-01

 

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-02

 

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:

parse-web

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-03

 

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

import-web-data-04

 

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

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

import-web-data-05

 

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-06

 

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

import-web-data-07

 

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

close & load

 

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.

workbookk-queries2

 

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

 

import-web-data2

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Use First Row as Headers Using Power Query or Get ... Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.One of the steps it allows you to do is to use the first row as headers.Let's suppose you have this set of data below.  You can see that the headers are not ...
Remove Rows With Errors Using Power Query Power Query lets you perform a series of steps to transform your messy Excel data.One of the most common steps I do, is  to clean my data and remove rows that have erroneous data, like this:Thankfully Power Query has an option that allows us to remove rows with errors...
Create Pivot Columns Using Power Query Power Query lets you perform a series of steps to transform your Excel data. One of the most common steps I do, is I want to simplify my data and aggregate them together into something like this:Thankfully Power Query has an option that allows us to create Pivot Columns! ...
Group Rows and Get Counts Using Power Query Power Query lets you perform a series of steps to transform your Excel data.One of the steps it allows you to take is to group rows and get the counts of each group very easily.DOWNLOAD EXCEL WORKBOOKLet’s go through the steps in detail: STEP 1: Select yo...