Blog - comments

I don't know any documentation about those EC and ECJ. And I'm sorry I don't know the consequence of...
Hi Franck thanks for clarifying this. I was already wondering about the difference between EC and EC...
Reiner
Sometimes with a group of transactions generating many archived logs, shipped and applied on standby...
Rick Chen
Thanks Franck, Let's change active/passive solution to other words. how about "RMAN script manual ma...
Rick Chen
Hi Franck, almost missed that article... - thank you! Having studied history for a lot of years I li...
Martin Preiss
Blog Stephane Haby With Power Query, converting an internet table to Excel is an easy job!

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
Posted by on in Middleware

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:

 

b2ap3_thumbnail_PoweQuery02.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Excel must be stopped to install this add-on:

 

b2ap3_thumbnail_PoweQuery01.jpg

 

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":

 

b2ap3_thumbnail_PoweQuery03.jpg

 

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

 

b2ap3_thumbnail_PoweQuery04b.jpg

 

Click on icon "From Web" and enter the Wikipedia URL:

http://en.wikipedia.org/wiki/List_of_The_Avengers:_Earth%27s_Mightiest_Heroes_characters

 

b2ap3_thumbnail_PoweQuery05.jpg

 

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

 

b2ap3_thumbnail_PoweQuery06.jpg

 

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

 

b2ap3_thumbnail_PoweQuery07.jpg

 

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

 

b2ap3_thumbnail_PoweQuery08.jpg

 

Or directly in a column:

 

b2ap3_thumbnail_PoweQuery09.jpg

 

Let's test it.

I will change the column type:

 

b2ap3_thumbnail_PoweQuery10.jpg

 

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

 

b2ap3_thumbnail_PoweQuery12.jpg

 

In the advanced option, the number of columns changes automatically when changing the delimiter. Very nice Wink!

And I now have 4 columns for the characters.

 

b2ap3_thumbnail_PoweQuery13.jpg

 

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

 

b2ap3_thumbnail_PoweQuery16.jpg

 

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

 

b2ap3_thumbnail_PoweQuery17.jpg

 

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

 

b2ap3_thumbnail_PoweQuery15.jpg

 

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.

b2ap3_thumbnail_PoweQuery19.jpg

 

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:

 

b2ap3_thumbnail_PoweQuery20.jpg

 

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

b2ap3_thumbnail_PoweQuery21.jpg

 

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. Cool

Rate this blog entry:
2

Stéphane Haby is Delivery Manager and Senior Consultant at dbi Services. He has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. Stéphane Haby is Microsoft Certified Solutions Associate MCSA) for SQL Server 2012 as well as Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008. He is also ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals & Pharmaceuticals, Banking / Financial Services, and many other industries.


MCSA  MCSE  mvp

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Sunday, 23 November 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter