Power Query Version Compatibility and Installation

Mynda Treacy

February 1, 2016

When you know what Power Query is capable of you’ll want to get your hands on it immediately. It’s available in 32-bit and 64-bit, but unfortunately it’s not available in all versions of Excel.

I’ve put together a comparison table to help you identify Power Query version compatibility.

Power Query Version Compatibility Table

Power Query version compatibility comparison table

How to check what version of Excel you have

Discovering which version of Excel you have is slightly different depending on the version.

Excel 2010

Go to the File tab > Help. On the right you’ll see the version and bitness:

Find version in Excel 2010

Excel 2013 and 2016

Go to the File tab > Account and then click on the About Excel button. This will display the About Microsoft Excel window where you can see the version, build and the bitness:

Find version in Excel 2013

Click here for a complete guide on finding which version of Office you’re using.

How to Install Power Query

Now you know which version of Excel you have and whether you can get Power Query you’re either annoyed you don’t have the correct version (I’ll tell you the cheapest way to solve that in a moment*), or you’re ready to go and install it.

Installing Power Query for Excel 2010 and 2013

In Excel 2010 and Excel 2013 Power Query is available as a free add-in. You can find out more about the system requirements and download it here.

Note: make sure you choose the correct version to match the bitness (32-bit or 64-bit) for your installation of Excel.

Once you’ve downloaded it simply run the MSI installer and follow the steps.

When the install is complete you’ll find a new tab for Power Query:

Power Query ribbon Excel 2013

Note: if you’ve installed Power Query and it’s gone missing you can usually re-enable it by going to the COM add-ins.

File tab > Options > Add-ins > COM Add-ins:

Power Query add-in Excel 2013

Excel 2016 Onward and Office 365

In Excel 2016 onward and Office 365 Power Query is integrated and is included in the Data tab, which means you don’t need to download or install it. You’ll also find that in Excel 2016 Power Query took on a new name: Get and Transform (personally I preferred Power Query, but anyway):

Power Query ribbon Excel 2016

*Power Query not available in your version of Excel

If you find you don’t have a version of Excel that has Power Query then the cheapest and easiest solution is to purchase the Standalone version of Excel 2013, or 2016 for around US$100 (price varies based on your local currency).

Go to www.MicrosoftStore.com and in the Search field type ‘Standalone Excel’. If you only need it for home use then you can choose the ‘non-commercial’ licence, which is cheaper.

The great thing about this version of Excel is that it comes with EVERYTHING. That means full Power Query functionality AND full Power Pivot functionality, as well as all the other standard Excel features.

Power Query in Action

So, now that you’ve installed Power Query here are some examples of ways you can use it:

Combine Excel Worksheets with Power Query

Split text with Power Query

Transpose Data with Power Query

Power Query Training

If you want to get up and running with Power Query fast then check out my Power Query course. I’ll have you up to speed in just 3.5 hours.

26 thoughts on “Power Query Version Compatibility and Installation”

  1. How do you unembed Power Query from Excel 2016? It is making our Excel environment unusable.
    Our data connection queries used to run in mere seconds. Now they are taking minutes to run. That is if they run at all. Even when trying to use the Legacy Wizards to connect to Access databases, Power Query seem to be getting in the way and clogging up the works.

    Reply
    • What makes you certain that it’s Power Query causing the issue? AFAIK these are two completely separate features that do not have any overlap, so a legacy query should not be interfered with by Power Query.

      Reply
  2. Hi Mynda
    I Really want to use Power Query but I am on Excel 2010.
    I have just gone to the MS Download site where is states:-
    “Microsoft Power Query for Excel is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, access and collaboration. (Note: The Microsoft Power Query add-in for Excel 2010/2013 is no longer being updated by Microsoft. Please upgrade to a newer Excel version.)”

    My query is when did MS stop up updating Power Query for Excel 2010/2013, AND will the current version of Power Query (PowerQuery_2.62.5222.761 (64-bit) [en-us].msi) still install and run OK on Excel 2010?

    Kind Regards,….Mark

    Reply
    • Hi Mark,

      I would expect it will work for you as it states that version on the install page specifically for Excel 2010. I don’t know when they stopped updating it, but I’d say it was many years ago! It’ll still be worth using though.

      Mynda

      Reply
  3. Greetings , hope you are doing well during this pandemic , my queries are

    Q.1 How do I activate Power Query in Excel , I am using Microsoft Office Home and Student Version 2019 ?

    Q.2 How do I copy and paste data in Excel , multiple times using Power Query ?

    Sincere and grateful thanks

    Carl Fernandes

    Reply
    • Hi Carl,

      You don’t need to activate Power Query in Excel 2019. It’s already available on the Data tab of the ribbon in the Get & Transform group of buttons on the left hand side.

      Power Query isn’t about copying and pasting, it’s about getting and cleaning data. If after you’ve got and cleaned the data once, you then load the data to the worksheet where you can copy and paste multiple times. Alternatively, you can duplicate the query and close and load to separate sheets if you want the same data multiple times, but I doubt this is what you really want.

      If that doesn’t answer your question, please post on our Excel forum where you can also upload a sample file and we can help you further.

      Mynda

      Reply
  4. Hi, I would like to check is there any differences between the Power Query in Excel 2016 and Excel 365 (besides the name?).
    BRs.

    Reply
  5. Hi,

    I using Excel 2016, My excel have Query already but I saw it different with Video training.
    Example: My Excel: in Query option, in Data Load have 2 item: Default Query load setting, Data Cache Management Option.
    But in video training in Query option, in Data load have 3 item: Type Detection, Relationships, Background data.
    So when I try combine from Multi Excel to one workbook, I can not do.

    Reply
    • Hi Van,

      These settings shouldn’t affect the ability to combine multiple files. Perhaps you can post your question on our Excel forumExcel forumExcel forum where you can include your files and screenshots of the errors you’re getting so we can help you further.

      Mynda

      Reply
  6. Hi Mynda,
    Just had my laptop upgraded to WIN10 from Win7. Also went from office 2013 to office 2016. When I open and try to refresh an excel model I get a message “The power query queries in this workbook might be incompatible with your current version of excel. These queries were authored with a newer version of PQ and might not work in your current version”.
    Also, when I refresh data I am now receiving a error “we couldn’t refresh connection [expression.Error] 2 arguments were passed to a function which expects 1.

    Any thoughts on how to correct this?

    Thanks,
    Joe

    Reply
  7. Thanks Mynda for preparing this schedule. Its hard to find this kind of information. Is the information on the chart still relevant ? I find it very discouraging when working with clients that are “hosted” and think they are set, cause hosting company is providing versions of Excel 2016 yet they do not have full PQ in them..and they can not refresh the templates I send them. It is soooo frustrating.

    Reply
    • Hi Fran,

      Yes the table is still relevant for Excel 2016.

      All versions of Excel 2016 can get data from the following sources: CSV & Text, Table/Range, Excel workbook, XML, JSON, Folder, Access, SQL Server, Web, OData feed, Analysis Services, Facebook, ODBC, OLEDB, and Microsoft Query.

      If your templates get data from databases or other cloud services then only Office 2016 Professional, Office 2016 Professional Plus, and Excel 2016 standalone licences have access to these, as explained here.

      Mynda

      Reply
  8. Stand-alone versions of Excel that are not married to an existing 365 installation feature an old version of Power Query. I first noticed this in September 2017, when I was creating workbooks for my office using Excel updated via 365, which could not be run by others, without various changes. Even the development environment for the queries has less features, such as not being able to choose a join type while constructing the merge. One has to add the code in Advanced Editor, if the one default join type does not work for you.

    Given I first noticed this in September 2017 and nothing appears to have changed as of April 2018, the advice here is solid. In short, don’t use Excel updated via 365 to provide stand-alone Excel users with Power Query based workbooks.

    Reply
    • You might want to update the standalone version of Excel. It will get some updates to Power Query, but not all new chart functionality etc. As it’s a standalone version you will have to manually do the update. Try File tab > Account. Usually there’s an ‘Updates’ button on that tab.

      Reply
  9. Can dashboards created with Power Query and Power Pivot be shared with users that do not have these add-ins installed on their computers without losing connection to the analysis server?

    Reply
    • Hi Vicky,

      Users without Power Query and Power Pivot can only look at the dashboards in a read only experience. i.e. they can’t click on Slicers to filter views etc.

      They are also likely to get error messages about connections being unavailable etc., which is fine to simply ignore, but some users might find this disconcerting.

      Kind regards,

      Mynda

      Reply
  10. Thank you Mynda for the summary. I have Excel 2013 and I downloaded Power Query last year. I was wondering: does Microsoft update the features of Power Query (I am not referring to the integration in Excel 2016)? If so, are the updates only available with a 365 subscription or does one have to re-install a newer version of Power Query?

    Ken

    Reply
    • Hi Ken,

      Yes, Power Query gets updated regularly. If you have Excel 2010 or 2013 then you just need to download the add-in and reinstall it to get the updated version.

      Kind regards,

      Mynda

      Reply
      • Mynda…that’s because those versions had the separate Add-in, right? What if you’re on Excel 2016 standalone? I’m presuming that no update is available in that case.

        Reply
        • Well so they (Microsoft) say, but I had a user this week with Standalone Excel installed in Dec 2016 and they got a May 2017 update which included Column from Examples, so I’d say it’s worth checking for updates to see if you get any.

          The official line is only Excel 2016 users with Office 365 licences get updates with new features. Standalone and other non-Office 365 licences get bug fixes only.

          Mynda

          Reply

Leave a Comment

Current ye@r *