Power Query

Power query is a new tool to improve your importation of data in your spreadsheet.

What is Power Query?

Power Query is a new add-ins for Excel 2010 & Excel 2013 and you can download it for free at this page: http://www.microsoft.com/en-us/download/details.aspx?id=39379.

This add-in is still in Preview version, so it is possible that there are some difference between your version and the screenshot in these articles.

Once you have downloaded and installed the add-in, you have to activate it in the menu File > Options > Add-ins. There, you select COM Add-ins in the dropdown list and you press Go …

Power_Query_Presentation_1

A new dialog box appears with all the add-ins install on your computer. Select the add-ins Microsoft Power Query for Excel and press OK.

Power_Query_Presentation_2

Then, a new tab Power Query appears on the ribbon of Excel.

Power_Query_Presentation_3

 

Import without Power Query

Most of the time, the data is imported in Excel through csv files. With the classic import wizard, you can only specify

  • the columns width
  • the delimiter
  • the type of data (standard, text, date)


More important, with this classical technique, you must re-execute the same process each time you want to re-import a csv file with the same structure. But with Power Query, the process of importation will be automatically generated for each new csv file.

Import with Power Query

When you import your data (csv file, excel file, access, sql server, microsoft azure, open data, ….) in Excel, you create a query for the importation.

A query is a kind of program to manipulate your data and you don’t need to create a macro Excel to reorganize your data after the import.

In the following articles, you will find a lot of new techniques to import easily your data in Excel.

Connection to an API with Power Query

  With Power Query, you can connect your workbook to an API.   What is an API? API means Application Programming Interface. An API is the interface (website) implemented by an application (the program own by the website) which allows other applications to communicate with it. In our case, the result will be in Excel :) An API is …

Power Query – Automatic update

First and most interesting aspect of Power Query is the fact that the result is in a table. Principe of Power Query When you import your data with Power Query in Excel, you will create a query. A query is, in the Power Query language, a program. Most of the people create a VBA program …

UDF

  With Power Query, you can easily convert a query in an UDF.   Presentation Let’s take back the example of the connection to a Google API. In this example, we have connect Excel with a Google API to return the distance between 2 cities. But at this step, it is really no convenient to use …

5 comments

Skip to comment form

    • Sahithya on 11/10/2014 at 06:22
    • Reply

    How do I extract required data from yelp dataset into excel using power query? i have tried doing the same but it shows an error. if someone can help me out!

    • Keith Gold on 14/08/2014 at 14:48
    • Reply

    Although I have Excel 2013 and my OS is 64 bit the Windows installer doesn’t install.

      • Jo on 17/09/2014 at 05:58
      • Reply

      you need excel professional plus..for it

      1. No, Power query can be installed on all the version of Excel 2013 and also Excel 2010

      • Malcolm on 01/01/2015 at 02:17
      • Reply

      I have a 64 bit OS, but a 32 bit 2010 Excel version. Hence, I needed the 32 bit version of Power Query.
      To confirm your Excel “bit” version, check File|Help, and look for the version information.

Leave a Reply

Your email address will not be published.