Power Query is one of the most useful tools in modern Excel. It pulls data from spreadsheets, databases, and APIs; cleans it; transforms it; and loads it into your workbook or data model — automatically. It's the right way to build repeatable data pipelines in Excel.
But Power Query has a complicated relationship with .xls files. If your source data is in the old binary Excel format, you're going to run into problems — wrong data types, broken refresh, errors that appear only on some machines. This post explains why, and what to do about it.
Power Query was built alongside the Open XML format (.xlsx) and the Excel data model (introduced in Excel 2010). It's designed to read structured, typed data from XML-based workbooks.
When you connect Power Query to an .xlsx file, it gets explicit column metadata: this column is text, this one is a date, this one is a number. The transformation steps it generates reflect that structure.
When you connect Power Query to an .xls file, it has to use a different, older code path — the legacy Excel connector — which reads the binary format through Windows drivers that weren't updated when Power Query was built. The result is unreliable. For a full feature-by-feature breakdown of what works and what doesn't, see our Power Query compatibility guide for legacy files.
The .xls format stores some numbers as text internally, even when they look like numbers in the cell. When Power Query reads them and tries to apply a type-change step, it fails because the raw value is a string like "1,234" instead of the number 1234.
In an .xlsx file, the cell's numeric value is stored separately from its display formatting, so Power Query reads the actual number. In .xls, this distinction is blurrier.
Excel stores dates as numeric serial numbers (days since January 1, 1900). In .xlsx, type metadata tells Power Query "this is a date column." In .xls, that metadata is often missing or ambiguous, so Power Query imports 45896 instead of February 23, 2026. You then have to add a manual type conversion step — and it breaks if the source format ever changes.
The .xls connector relies on 32-bit or 64-bit Windows drivers (ACE or JET) depending on your Office installation. A query that works on your 64-bit machine may fail on a colleague's 32-bit Office installation — or vice versa. This is a driver bitness mismatch, and it's specific to legacy file format connectors. It doesn't occur with .xlsx because those files use the native Excel engine.
When Power Query reads an .xls file, it sometimes infers the wrong number of columns — including empty columns from the edge of the used range, or missing columns where the cell content is ambiguous. The same file in .xlsx produces a clean, consistent column list.
| Feature | .xls source | .xlsx source |
|---|---|---|
| Basic data import | Works (usually) | Works |
| Automatic type detection | Unreliable | Reliable |
| Date column recognition | Often fails | Works |
| Cross-machine refresh | Driver-dependent | Consistent |
| Query folding | Not supported | Not applicable (local file) |
| Power BI integration | Problematic | Supported |
| Incremental refresh (Power BI) | Not supported | Supported |
| Named table connectors | Limited | Full support |
The simplest and most complete fix is to convert your .xls source file to .xlsx, then reconnect the Power Query query to the .xlsx version. This eliminates all of the driver and type-detection issues at the source.
Here's the sequence:
This is where it gets tricky. If you open the .xls file in Excel and use File → Save As → Excel Workbook (.xlsx), you get a warning that macros will be removed. If you save as .xlsm instead, the macros are preserved — but only if Excel can properly convert the VBA project from the old format.
For simple macros, this works. For complex workbooks with user forms, class modules, 64-bit declarations, or ActiveX controls, the Save As conversion often produces a broken .xlsm that won't compile. The macros are "there" but they don't run.
The reliable approach is a dedicated migration tool that converts the binary container, migrates the VBA project structure properly, and validates the output — rather than using Excel's built-in Save As, which is not designed for this purpose.
LegacyLeaps converts .xls to .xlsx or .xlsm while preserving formulas, macros, and formatting. Once your source files are in modern format, Power Query works reliably — no more driver errors, type mismatches, or refresh failures.
Run Free ScanThis is a question worth addressing directly, because if you're modernizing legacy .xls workbooks, you may also be deciding whether to keep VBA-based data pipelines or replace them with Power Query.
They're not interchangeable:
Many modern Excel workbooks use both: Power Query handles data ingestion and transformation, VBA handles the automation layer on top. Migrating from .xls to .xlsx/.xlsm lets you keep both — with Power Query working properly against the modern format source, and VBA running cleanly in the macro-enabled container.
If you're thinking of saving to .xlsb as an intermediate step, be aware: Power Query has similar (though less severe) issues with .xlsb files. The binary workbook format is faster to open and save for large files, but Power Query's type detection is more reliable with .xlsx. Use .xlsb for large static workbooks; use .xlsx for Power Query source files.
Yes, but with significant limitations. Power Query uses a legacy connector for .xls that relies on older Windows drivers. Column types are often misdetected, refresh behavior is inconsistent across machines, and some Power Query features don't work reliably with the binary format. Converting to .xlsx resolves these issues.
The .xls binary format doesn't store explicit column type metadata the way .xlsx does. Power Query has to infer types from raw cell data, which leads to frequent mismatches — numbers stored as text, dates imported as serial numbers, mixed-type columns that fail type-change steps. Converting to .xlsx resolves this because the XML-based format stores type information explicitly.
Some steps may need to be updated — particularly if you hard-coded the file path with the .xls extension, or if you added workarounds for type misdetection. The core transformation logic typically survives. After converting, update the data source path in Power Query and re-run the type detection steps to get clean results from the .xlsx source.
Practical fixes for legacy Excel and Access problems. No spam.