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
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:
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:
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:
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:
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 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
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.
Transpose Data in Excel
Cris
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.
Mynda Treacy
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.
Mark Jackman
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
Mynda Treacy
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
Carl Fernandes
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
Mynda Treacy
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
Huyen
Hi, I would like to check is there any differences between the Power Query in Excel 2016 and Excel 365 (besides the name?).
BRs.
Mynda Treacy
Hi Huyen, you can see the version comparisons here. Mynda
Van
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.
Mynda Treacy
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
Joe
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
Catalin Bombea
Hi Joe,
Did you installed all updates after installing office? Check and install updates, then try again that file, you might be missing updates.
Joe
The option to update is not available…I believe IT has disabled it so they can do enterprise wide updates. This is a real problem as most if not all my models are rendered useless.
Catalin Bombea
Then you need to ask them to update, there is no other way.
Beth Lewis
Hi Joe – did you determine if there IS an update? I am not in the EXACT same position, and I am trying to determine if there is an update available from MS. In this blog from Mynda back in 2017 (and the last comment from her below – https://www.myonlinetraininghub.com/power-query-version-compatibility-and-installation#comment-48495), it doesn’t appear to be:
https://www.myonlinetraininghub.com/power-query-updates
I am on Microsoft Office Professional Plus 2016 – an enterprise install.
On one of my tools, when I got the message, it did take me to the Promote Headers step, so I deleted that and added it back in and the error went away. I am in the process of testing the rest of the functionality, but I can get the query results by deleting the Promote Headers step and inserting a new one!
I was hoping someone found out they were providing updates!
Mynda Treacy
Hi Beth,
Enterprise installs get updates. You’ll just need to speak to your IT people.
Mynda
Fran Reed
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.
Mynda Treacy
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
ScotsSailor
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.
Mynda Treacy
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.
Vicky
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?
Mynda Treacy
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
Ken Witchel
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
Mynda Treacy
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
Jeff Weir
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.
Mynda Treacy
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