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

Replicating Excel’s FIND Function with M in ... Power Query lets you perform a series of steps to transform your Excel data.  There are times when we want to do things that are not built in the user interface.  This is possible with Power Query's formula language, which is called M.Unfortunately not all of Excel's formulas...
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...
Split Column By Number of Characters Using Power Q... Power Query (in Excel 2010 & 2013) 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 split column by number of characters easily.This is helpful when you have columns that ...
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...