The following information details which versions of Excel include the Power Query (known as Get & Transform in Excel) experience.
Excel 2016 or later for Windows and Microsoft 365
The Power Query experience is available in all Excel 2016 or later Windows stand alone versions and Microsoft 365 subscription plans on the Data tab in the Get & Transform group. The following table shows basic and advanced features available by version.
| Connector Name | Office 2016 Home & Student Home & Business Standard Professional |
Office 2016 Professional Plus Standalone |
Office 2019 Home & Student Home & Business Standard Professional |
Office 2019 Professional Plus Standalone |
Microsoft 365 Home Personal Business Business Premium |
Microsoft 365 Apps for Enterprise |
|---|---|---|---|---|---|---|
| Excel workbook |
|
|
|
|
|
|
| Text & CSV |
|
|
|
|
|
|
| XML |
|
|
|
|
|
|
| JSON |
|
|
|
|
||
| PDF (Requires .Net Framework 4.5+) |
|
|
||||
| Local folder |
|
|
|
|
|
|
| SharePoint Folder |
|
|
||||
| SQL Server |
|
|
|
|
|
|
| Microsoft Access |
|
|
|
|
|
|
| Analysis Services |
|
|
|
|
|
|
| SQL Server Analysis Services (Import) |
|
|
|
|||
| Oracle |
|
|
|
|||
| IBM Db2 |
|
|
|
|||
| MySQL |
|
|
|
|||
| PostgreSQL |
|
|
|
|||
| Sybase |
|
|
|
|||
| Teradata |
|
|
|
|||
| SAP HANA |
|
|
||||
| Azure SQL Database |
|
|
|
|||
| Azure SQL Data Warehouse |
|
|
||||
| Azure HDInsight (HDFS) |
|
|
|
|||
| Azure Blob Storage |
|
|
|
|||
| Azure Table Storage |
|
|
|
|||
| Azure Data Lake Storage |
|
|
||||
| Azure Data Explorer |
|
|||||
| SharePoint Online List |
|
|
||||
| Microsoft Exchange Online |
|
|
||||
| Dynamics 365 (online) |
|
|
|
|||
|
|
|
|
|
|
|
|
| Important: Retirement of Facebook data connector notice Import and refresh data from Facebook in Excel will stop working in April, 2020. You will still be able to use the Facebook Power Query connector until then. But starting in April, 2020, you will be unable to connect to Facebook and you will receive an error message if you try using the Facebook connector. We recommend revising or removing any existing Power Query queries that use the Facebook connector as soon as possible to avoid unexpected results. | ||||||
| Salesforce Objects |
|
|
|
|||
| Salesforce Reports |
|
|
|
|||
| Table/Range |
|
|
|
|
|
|
| Web |
|
|
|
|
|
|
| Microsoft Query |
|
|
|
|
|
|
| SharePoint List |
|
|
|
|||
| OData Feed |
|
|
|
|
|
|
| Hadoop File (HDFS) |
|
|
|
|||
| Active Directory |
|
|
|
|||
| Microsoft Exchange |
|
|
|
|||
| ODBC |
|
|
|
|
|
|
| OLEDB |
|
|
|
|
|
|
| Blank Query |
|
|
|
|
|
|
Choosing between Microsoft 365 and Professional Plus Standalone
Because it's a subscription-based version, Microsoft 365, at any one time, contains all the available Power Query features. You can think of it as the master set. Professional Plus Standalone (also called perpetual) versions have a large subset of features, but not all of them. For example, fuzzy merge is supported in Microsoft 365, but not in 2019 perpetual. More features were added to 2019 perpetual than were available in 2016 perpetual, and even more will be added to the next perpetual version called 2021. Given this situation, you have two choices. The first choice is to wait for the next upgrade to a perpetual version if you want a specific feature. This choice might work, but truthfully, it may not. We can't guarantee that a specific feature will be added to the next perpetual version. The second choice is to subscribe to Microsoft 365. We encourage you to make that choice so that all the Power Query features are available to you. For more information, see Microsoft 365 with Office apps.
Excel for the Web
The following table summarizes what data sources are supported and what you can do with them.
Note
- Refresh for queries loaded to the Data Model is not yet supported.
- Refresh for queries in workbooks saved in a third-party cloud location is not yet supported.
- Refresh for data sources that require an on-premise data gateway is not supported.
| Data source | Authentication |
|---|---|
| Table/Range | Anonymous |
| OData Feed | Anonymous |
| SharePoint* files (Excel workbooks, TXT, CSV, XML, JSON, PDF) | Organizational |
| SharePoint* folders | Organizational |
| SharePoint Online List | Organizational |
| SharePoint List | Organizational |
| SQL Server Database | User/Password |
| OData Feed | User/Password |
| Web API | User/Password |
| IBM Db2 Database | User/Password |
| PostgreSQL Database | User/Password |
| AzureSQL Database | User/Password |
| Azure Synapse Analytics | Organizational |
| Azure HDInsight (HDFS) | Organizational |
| Azure Blob | Organizational |
| Azure Table | Organizational |
| Azure Data Lake Storage Gen 1 | Organizational |
| Azure Data Lake Storage Gen 2 | Organizational |
| Azure Data Explorer | Organizational |
| Dataflows | Organizational |
| Dataverse | Organizational |
| Microsoft Exchange Online | Organizational |
| Dynamics 365 (Online) | Organizational |
| Salesforce Objects | User/Password |
| Salesforce Reports | User/Password |
*SharePoint/OneDrive for work or school
Note
Refresh is limited to 1000 connections per user. The refresh happens behind the scenes so you can keep editing the workbook while refreshing.
Excel for Microsoft 365 for Mac
If you're a Microsoft 365 subscriber, the Power Query experience is available in Excel for the Mac from the Get Data command on the Data tab. The following table summarizes what data sources are supported and what you can do with them. For more information, see Import data in Excel for Mac (Power Query).
| Data source | Refresh | Import |
|---|---|---|
| Text & CSV |
|
|
| Excel workbook |
|
|
| XML |
|
|
| JSON |
|
|
| SharePoint Folder |
|
|
| SQL Server |
|
|
| Table/Range |
|
|
| Local Folder |
|
|
| SharePoint List |
|
|
| OData Feed |
|
|
| Blank Query |
|
|
| Blank Table |
|
|
Excel for Android and iOS
Power Query is not supported.