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.   Contents1 What is an API?2 Connection to a Google API3 Write the code in Power Query4 Explore the Xml 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 …

View page »

Power Query – Automatic update

First and most interesting aspect of Power Query is the fact that the result is in a table. Contents1 Principe of Power Query2 Result in an Excel’s table3 Example with a csv file4 Refresh the connection5 Change the source6 Conclusion Principe of Power Query When you import your data with Power Query in Excel, you will …

View page »

UDF

  With Power Query, you can easily convert a query in an UDF.   Contents1 Presentation2 Create an UDF3 Call the Power Query 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 …

View page »

5 comments

Skip to comment form

  1. Sahithya

    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!

  2. Keith Gold

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

    1. Jo

      you need excel professional plus..for it

      1. Frédéric LE GUEN

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

    2. Malcolm

      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. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>