PowerQuery – Using data from the Web


Copy – Paste?
How many times did you paste information from a website into an Excelsheet? It is a common scenario where people are surfing the web and want to keep the information they bump in to. A nice table presented by the website with all possible combinations, pricelists, adresses you name it. Well copy the table from the web and paste it into the Excelsheet right. But what if it is a very long table which exceeds the limits of your screen? Dragging your mouse very slowly and hoping that those annoying adds wont be in the selection as well. Enter PowerQuery!

PQ_ExcelAddIn No more copy and pasting, no more annoying adds accidently being selected as well.
PowerQuery is an add in for Excel and you can download it here . When installed you will see an additional tab in your Excelribbon. For this example i want to use the data presented on the microsoft learning site about MOS certifications. There are various tracks to choose from. I want to have all the data. So let’s see if we can grab the info for Office 2013, 2010 and 2007.
1. Open Excel and click on the PowerQuery tab in the ribbon
2. In the Get External Data group click on the From Web button
PQ_I
3. Copy the URL from your addressbar from your browser
 PQ_II
4. In the dialog window in Excel paste the URL you’ve just copied and click OK
PQ_III
5. Wait for it…. A Navigation taskpane will show up on the right side of your screen
PQ_IV

6. The nav. pane will show you the available data from the URL you’ve copied. In this case we are presented with three tables and a document. The document does not contain the data we are looking for. The tables however….

7. Hover over the first table (in the screenshot table 0). Excel will show you a preview of the data the table contains.
 PQ_V
8. If you are satsisfied with the result click on the button Load on the bottom of the taskpane. The table will be inserted into your worksheet!
PQ_VI
That’s it.. no more hassle. If the table exceeds your screen don’t bother, PowerQuery will grab the whole table. No more copy and pasting. Yeah!What’s next?

Well it might be that you are almost satisfied but not quit yet. When hovering over the table you will see an option to edit the table before you load it into your worksheet. When you click edit you will see a new PowerQuery layout pop up with a lot of tweaking options for your datasource. Pick the ones you like and then click load to worksheet.

PQ_VII

Next time we will grab a more complicate table and edit the data before entering it into our worksheet. Stay tuned!
Happy Querying 🙂

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s