Blog - comments

hi i am also facing the same issue please tell me how u resolved this.

sandeep singh

How you resolved this

sandeep singh
Hi Nag,Thanks for your comment. Do you have a problem when creating the procedure or when executing ...

Yes it is not line with that statement but I didn't write the first quote ;)

Anton Ivanovitch

thank you very much. It was like a walk in the park

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.



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:




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 Wink!

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.



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:

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.



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

Leave your comment

Guest Friday, 03 July 2015
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)


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