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

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Fill Down Values 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 fill data down easily. You might be wondering when you might need to fill data down in your table. Let's suppose you have this set of data: A lo...
Advanced Editor In Power Query Power Query can let us perform a lot of complex steps with our data. However wouldn't it be fun if you could understand better what is happening under the hood? Come join me as we take a look into the Advanced Editor! We will use the Index Column Post as our starting point....
Merge Columns 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 merge columns easily. Normally I would use the CONCATENATE formula to merge columns together, however a cleaner way is to simply use Power Query for this....
Keep Duplicate Records 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 take is to keep duplicate records. We usually remove duplicate lines but if we need to keep and check what the duplicates are,...