You converted your legacy Excel files from .xls to .xlsx. The workbook opens. The formulas work. Then you refresh your Power Query and get a wall of red errors. DataSource.Error: File not found. Expression.Error: The column 'Order ID' of the table wasn't found. DataFormat.Error: We couldn't convert to Number.
This is the part most migration guides skip: Power Query stores your data sources and transformation logic in M code, and that M code references the exact file path, file extension, sheet names, and data types of your original files. When the file format changes from .xls to .xlsx, Power Query treats it as a different source — and every assumption baked into your queries may need revisiting.
This guide covers the three most common categories of Power Query breakage after migration and how to fix each one.
Power Query is a transformation engine. It records every step you take as M code in the Applied Steps pane. When you originally built a query against sales-data.xls, the Source step in your M code looks like this:
= Excel.Workbook(File.Contents("C:\Reports\sales-data.xls"), null, true)After migration, that file is now sales-data.xlsx. Power Query looks for sales-data.xls, finds nothing, and the entire query chain fails. It doesn't matter that the content is identical — to Power Query, it's a different file.
That's the straightforward case. The more subtle problems come from three other sources: data type inference differences between formats, sheet and named range references, and M function behavior that varies between legacy Jet-backed .xls and Open XML-backed .xlsx.
DataSource.Error: Could not find file '...\filename.xls'
.xls to .xlsxIf the query uses the Advanced Editor (right-click the query → Advanced Editor), find the File.Contents line and update the extension there.
If you have multiple queries pulling from the same source file — or if this file path will change again in the future — use a parameter instead of hardcoding the path:
SourceFilePath, set Type to Text, set the Current Value to your .xlsx file path= Excel.Workbook(File.Contents(SourceFilePath), null, true)Now when the source file moves or changes format, you update one parameter instead of hunting through every query.
LegacyLeaps converts legacy Excel files to .xlsx while preserving all your formulas, VBA macros, and named ranges. Scan free to see exactly what's in your files before you migrate.
Run the Free ScanThe .xls format uses the legacy Jet engine for data type inference. The .xlsx format uses Open XML, which stores cell types explicitly. This means the same data can be interpreted differently after migration — and Power Query's Changed Type steps can start throwing errors.
| What you had in .xls | What .xlsx may return | Power Query error |
|---|---|---|
| Numbers stored as text (leading zeros, phone numbers) | Numeric values (leading zeros stripped) | DataFormat.Error in Changed Type step |
Dates stored as text ("2024-01-15") |
Date serial numbers or actual Date values | Type mismatch in date comparison steps |
| Blank cells treated as null | Empty string "" in some columns |
Errors in filter/replace steps expecting null |
| Mixed numeric/text in a column | Column inferred as Text or Number based on majority | Conversion errors in downstream steps |
After updating your Source step to point to the .xlsx file, step through each Applied Step and watch the data preview for type indicator icons (the ABC/123 icons in column headers). Any column that changes its type indicator needs attention.
The safest fix is to add an explicit Changed Type step immediately after the Source and Navigation steps — before any filtering or transformation — using Table.TransformColumnTypes:
= Table.TransformColumnTypes(#"Previous Step", {
{"Order ID", type text},
{"Amount", type number},
{"Date", type date},
{"Customer Code", type text}
})Explicit typing removes the ambiguity of format-dependent inference. It also makes your queries more resilient to future format changes.
ZIP codes, employee IDs, part numbers, and phone numbers stored in .xls as text with leading zeros may come through as numbers in .xlsx. Fix this by setting the column type to type text before any transformation, and add a padding step if needed:
= Table.TransformColumns(#"Changed Type", {
{"ZIP Code", each Text.PadStart(Text.From(_), 5, "0"), type text}
})If you renamed any sheets during the .xls to .xlsx conversion — even to fix a typo — Power Query's Navigation step will fail. The Navigation step references sheets by name:
= Source{[Item="Sheet1",Kind="Sheet"]}[Data]If the sheet is now called Data instead of Sheet1, update this step to match. You can also navigate by index (Source{0}[Data]) if your sheet is always first, though name-based navigation is more maintainable.
If your .xls file had defined named ranges and your queries used Excel.CurrentWorkbook() to load them, check that the named ranges survived the migration intact. Open the .xlsx file, go to Formulas → Name Manager, and verify your range names are still present with the correct scope.
Watch out: Named ranges that referenced external workbooks in .xls sometimes lose their definitions during format conversion. If you see #REF! errors in the Name Manager after migration, the named range needs to be recreated before your Power Query will work.
Some .xls files use hidden sheets as data staging areas. Power Query may reference these sheets by name. After migration, verify that hidden sheets transferred to the .xlsx file. In Excel, right-click any sheet tab and select Unhide to see all hidden sheets.
If you used Get Data → From Folder to load all .xls files from a directory, your query is likely filtering by file extension:
= Table.SelectRows(Source, each ([Extension] = ".xls"))After migration, those files are now .xlsx. Update the filter:
= Table.SelectRows(Source, each ([Extension] = ".xlsx"))If you're in a mixed environment where some files are already converted and others aren't yet, you can temporarily use an or condition:
= Table.SelectRows(Source, each ([Extension] = ".xls" or [Extension] = ".xlsx"))Clean this up to .xlsx only once all files in the folder are converted.
Once you've updated source paths, corrected data types, and fixed navigation steps, don't just check that the query runs — validate the output:
This guide assumes you already have .xlsx files to point your queries at. If your .xls files are the source of truth and you haven't converted them yet, a few things matter for Power Query compatibility:
LegacyLeaps's Excel conversion preserves named ranges, formatting, and sheet names exactly as they exist in the source file — so your Power Query connections survive the migration intact.
LegacyLeaps scans your .xls files and shows you exactly what's inside — named ranges, macros, ActiveX controls, external links — before you convert a single file. Free scan, no sign-up required.
Download Free ScannerPower Query stores the data source file path in the M code. When you convert .xls to .xlsx, the file extension changes — even if the name and location stay the same. Power Query can't find a file called data.xls if it has been renamed to data.xlsx. You need to update the Source step in each affected query to point to the new .xlsx file.
They can. The .xls format uses the Jet engine for type inference, while .xlsx uses the Open XML format with stricter data type handling. Numbers stored as text in .xls sometimes become numeric in .xlsx, which breaks Power Query steps that expect text. Always check the Changed Type steps in your queries after migration and add explicit type conversions where needed.
Open Power Query Editor (Data → Get Data → Launch Power Query Editor), select the affected query, click the Source step in the Applied Steps pane, and update the file path from .xls to .xlsx. If the same source file feeds multiple queries, use a parameter to store the file path so you only need to update it once.
Yes. Use the Advanced Editor to view and edit M code directly. For workbooks with many queries sourcing the same .xls file, replace the hardcoded path in the Source step across all queries. A better long-term fix is to use a file path parameter that all queries reference — then you only update the parameter when the source file changes.
The core function stays the same: Excel.Workbook(File.Contents(...)). But you may need to set UseHeaders or InferColumnTypes parameters explicitly in the .xlsx version if the defaults produce different results than you saw with the .xls source. Named table references via Excel.CurrentWorkbook() work the same in both formats.
Practical fixes for legacy Excel and Access problems. No spam.