Power Query is one of Excel's most powerful modern features — but it has a complicated relationship with legacy .xls files. If you're trying to modernize your Excel workflows while your organization still has .xls workbooks everywhere, you've probably already hit some of the friction: queries that disappear on save, refreshes that fail silently, or data source connections that work on your machine but not a colleague's.
This guide explains exactly where the .xls/.xlsx boundary breaks Power Query functionality, what the safe workarounds are, and when migration to .xlsx is the only real fix. For more on the specific errors Power Query throws when reading legacy files, see our companion post on why Power Query breaks with legacy .xls files.
Power Query was introduced as an add-in for Excel 2010 and 2013 in 2013, and became a built-in feature in Excel 2016. The .xls binary format was retired by Microsoft in 2007. These two things were never designed to coexist, and the incompatibilities aren't bugs — they're architectural reality.
When you work in an .xls workbook, Excel opens it in a backward-compatibility mode that disables Power Query's storage layer. The query editor will open. You can build transformations. You can load data. But the query definitions cannot be saved into the file — and Excel will not warn you that this is happening.
This is the most common complaint: "My Power Query queries keep disappearing." The answer is almost always that the workbook is saved as .xls.
| Feature | .xls workbook | .xlsx workbook | Notes |
|---|---|---|---|
| Import data from external sources | Works | Works | You can open the query editor from either format |
| Save query definitions in workbook | Broken | Works | Queries are silently dropped when saving as .xls |
| Query refresh on open | Broken | Works | No saved queries = no refresh |
| Import from .xls file as data source | Works | Works | Works when the .xls is a source, not the host workbook |
| Import from .mdb as data source | Partial | Partial | Requires 32-bit ACE driver on 64-bit Excel; .accdb is more reliable |
| Power Pivot data model | Broken | Works | Data model requires .xlsx or .xlsm; not stored in .xls |
| DAX measures and calculated columns | Broken | Works | Depends on Power Pivot data model |
| Query parameters | Broken | Works | Parameters are part of the query definition storage layer |
| Queries & Connections pane | Not shown | Works | The pane appears empty in .xls mode even if queries were built |
| Shared queries (organization-level) | Broken | Works | Requires .xlsx/Power BI service integration |
The most dangerous scenario isn't a visible error — it's a quiet one. Here's what happens in practice:
This loop can repeat indefinitely if no one realizes the root cause. You're not doing anything wrong — the format just can't hold what you're creating.
There's an important distinction: hosting queries in an .xls workbook fails; importing from an .xls file as a data source works fine.
If you have a reporting workbook (.xlsx) that pulls data from several legacy .xls files on a shared drive, Power Query handles this well:
This pattern works — until the .xls source file itself has compatibility issues on Windows 11, or until someone asks why the reporting workbook can't be one self-contained file. At that point, you're back to needing the .xls files converted.
LegacyLeaps converts .xls to .xlsx while preserving all formulas, named ranges, and VBA macros — so your Power Query data sources stay consistent after migration. Free scan shows exactly what each file contains.
Try the Free ScanIf you use Power Query to pull data from Access databases, the .mdb vs .accdb distinction matters significantly on modern Windows.
Power Query connects to Access files through the ACE (Access Connectivity Engine) driver. On 64-bit Excel, only the 64-bit ACE driver is used. .mdb files, however, were built for the older 32-bit Jet engine — and while the 64-bit ACE driver has some backward compatibility, edge cases arise:
Converting your Access database from .mdb to .accdb format eliminates the driver mismatch. .accdb is the native format for modern Access and is fully supported by the 64-bit ACE driver in Excel 2016, 2019, and 365. After conversion, Power Query connections to the database become more stable and don't require separate driver installations.
After converting, update your Power Query data source path:
If you're ready to convert your workbooks and unlock full Power Query functionality, work through this checklist before and after conversion:
If your workbook has both VBA macros and Power Query queries, save it as .xlsm (macro-enabled workbook). .xlsm is a macro-enabled variant of the .xlsx format — it stores both query definitions and VBA code correctly. This is the format to use for any workbook that combines modern Power Query data loading with legacy automation code.
Do not save macro-enabled workbooks as .xlsx — Excel will warn you that macros will be removed, but it's easy to click past this warning without realizing what you've lost.
| Situation | Action |
|---|---|
| Your Power Query workbook is saved as .xls | Convert to .xlsx immediately — queries cannot be saved in .xls |
| You import from .xls files as data sources | Works for now, but convert sources to .xlsx to eliminate future compatibility risk |
| You import from .mdb via Power Query | Convert .mdb to .accdb to eliminate 32-bit driver issues on Windows 11 |
| Your workbook has VBA + Power Query | Save as .xlsm, not .xlsx or .xls |
| You use Power Pivot or DAX | Must be .xlsx or .xlsm — .xls cannot store the data model |
LegacyLeaps converts .xls to .xlsx and .mdb to .accdb while preserving your formulas, macros, and formatting. Your files stay on your machine — nothing is uploaded. Run the free scan to see exactly what each file contains before you convert.
Download Free ScannerYes, Power Query can import data from .xls files as a data source. But if the workbook you are working in is saved as .xls, Power Query queries cannot be stored or refreshed in that file. Always build your Power Query workbooks in .xlsx format, even if you're importing from .xls sources.
Power Query stores queries in the workbook's internal XML structure, which the .xls binary format does not support. When you save to .xls, Excel silently drops all query definitions. The data that was loaded remains, but all queries and connection metadata are lost. Save Power Query workbooks as .xlsx or .xlsm.
Power Query can connect to both. However, .mdb connections require the 32-bit ACE or Jet driver, which is not available in 64-bit Excel without installing additional redistributables. .accdb connections work natively with 64-bit Excel on Windows 11. If you're having connection issues with .mdb files, converting to .accdb is the cleanest fix.
No — queries built in an .xlsx host workbook that import from .xls source files will continue to work after those sources are converted. Just update the data source path in Power Query after conversion: Data tab → Queries & Connections → right-click the query → Edit → Home → Data source settings.
Practical fixes for legacy Excel and Access problems. No spam.