With Power Query, converting an internet table to Excel is an easy job!

While exploring SQL Server 2014, I was surprised by the powerful Power Query feature. In the recent past, converting an internet table to Excel was a nasty work leading to format problems, implicit conversions, etc. With Power Query, importing tables from the internet is very easy!

What is Power Query?

Power Query is a tool based on the concept of Power BI (more on this tool here).

Power BI is composed of:

• PowerPivot

The first version was introduced with SQL Server 2008 R2. PowerPivot allows for self-service data modeling.
(Msdn link here)

• Power View

Part of SQL Server 2012 “Reporting Services”, Power View allows for ad hoc, self-service data visualization.
(Msdn link here)

• Power Map

The “GeoFlow” project is now called Power Map. This tool makes it extremely easy to view data containing almost any geographical attributes.
(Msdn link here)

• Power Query

Power query allows data to be imported into PowerPivot data models from different sources
(Msdn link here)

Let’s see how to install this last one.

Installation

You can download the msi file (~8 MB) on the Microsoft website for Excel 2013/2010 here.

The supported Office versions are:

• Office 2013 Professional Plus
• Office 365 ProPlus
• Excel 2013 Standalone
• Office 2010 Professional Plus (with Software Assurance for Office Professional Plus through Volume Licensing)

It is available for 32-bit (x86) and 64-bit (x64) platforms.

Execute the msi package to install it:

Excel must be stopped to install this add-on:

And now, after the installation, I have to reopen Excel to continue my presentation.

How does it work?

In Excel, I have a new tab named “Power Query”:

Today, I will import all Avengers character heroes from Wikipedia to my Excel sheet:

Click on icon “From Web” and enter the Wikipedia URL:
http://en.wikipedia.org/wiki/List_of_The_Avengers:_Earth%27s_Mightiest_Heroes_characters

While Power Query is working, I can see the “waiting wheels” known as preloader on the button OK.

And in this query editor, I have all tables from the web site like Avengers, new Avengers, SHIELD, HYDRA, Masters of Evil, etc.

Like in the Excel sheet, I can modify my table format directly in the Query Editor:

Or directly in a column:

Let’s test it.
I will change the column type:

I will split my first column with the delimiter ‘/’ to have one column per character:

In the advanced option, the number of columns changes automatically when changing the delimiter. Very nice :roll:!
And I now have 4 columns for the characters.

Since I don’t need the Voice column, I will delete it:

I have the possibility to see the change history on the right-hand side with the Steps menu:

You can look up the web site history in the Data Source Setting icon to see all sources used.

Another good way to look up the Avengers characters is to search online.

Online Search

The Online Search option is the first icon of the Power Query tab.

On the right-hand side of the sheet, a new window with the online search appears. Type your keywords and click search.
A result list with all the tables on the internet is displayed:

Just click on a result and you will directly have your internet table in Excel.

In this option, you cannot modify the column before the importing into the excel sheet is finished.

Conclusion

In my opinion, Power Query indeed is a POWERful tool making it very easy to import tables from the Internet. 😀