'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Power Query Compatibility Issues Between Excel 2021 and 2016
Question details
The user is unable to refresh Power Query data in Excel 2016 that was created using newer features in Excel 2021.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Opening and refreshing a workbook in Excel 2016 that was authored in Excel 2021 with advanced Power Query features.
- Observed behavior
- The workbook opens, but Power Query connections fail to refresh due to unsupported functions or transformations in the older Excel version. The built-in Compatibility Checker does not flag this issue.
Verify the exact error message displayed when the refresh fails, as this will help you pinpoint which specific query or step is causing the compatibility issue in Excel 2016.
Identify and Replace Unsupported Power Query Functions
Use this solution to manually adjust the Power Query steps in Excel 2016 so they rely only on older, universally supported functions.
Excel 2021 includes newer Power Query M functions and connectors that are not recognized by Excel 2016 (Power Query version 2.94). To make the file work in the older version, you must find the exact step causing the failure and replace it with a compatible alternative.
In Excel 2016, navigate to the 'Data' tab on the ribbon and click on 'Queries & Connections'. Double-click the query that is failing to refresh to open the Power Query Editor.
Look at the 'Applied Steps' pane on the right side of the editor. Click through each step from top to bottom. The first step that displays an error message is the one containing the unsupported feature.
Delete or modify the failing step. Recreate the data transformation using older, fundamental Power Query features available in Excel 2016 (such as standard filtering, basic merges, or older text-extraction methods).
Once the error is cleared and the data previews successfully, click 'Close & Load' in the top left corner to apply the changes to your workbook.
Refresh Data in a Newer Excel Environment
Use this approach if you cannot modify the advanced Power Query steps and only need to provide static updated data to Excel 2016 users.
Try WPS Office for Seamless Data Management
If you are tired of version compatibility issues and expensive upgrades across different Office releases, WPS Office provides a free, lightweight, and highly compatible alternative. Enjoy a familiar interface and seamless migration for your daily spreadsheet needs.
- 1. Download WPS Office: Visit the official WPS website and download the free installation package for your operating system.
- 2. Install the software: Run the installer and follow the quick on-screen instructions to set up WPS Office.
- 3. Open your workbooks: Launch WPS Spreadsheet and easily open your existing .xlsx files to continue working seamlessly.
Frequently Asked Questions
Why doesn't the Excel Compatibility Checker report Power Query issues?
The built-in Excel Compatibility Checker primarily scans for native spreadsheet features like charts, formatting, and standard formulas. It does not deeply evaluate the underlying M code or specific Power Query transformations for version compatibility.
Will saving the file in an older format fix the query?
No. Saving the workbook in an older format (such as .xls or standard .xlsx via 'Save As') does not translate or downgrade the newer Power Query M code into an older version. The unsupported functions will remain broken in Excel 2016.
Can I update Power Query in Excel 2016 to match Excel 2021?
Excel 2016 is a perpetual license product. While it receives critical security updates, Microsoft does not add new features, connectors, or advanced Power Query transformations to it. Upgrading your Office suite is the only way to get native support for those new features.
How do I find out which Power Query step is incompatible?
Open the Power Query Editor in Excel 2016 and select your failing query. Click through the 'Applied Steps' list on the right side from top to bottom. The very first step that results in a yellow error message is the one containing the incompatible feature.
