Fix Power Query Importing Only 255 Columns from Excel Workbook
Question details
The user needs to resolve an issue where Power Query fails to load all columns from an updated source file.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Replacing a source XLSX file for Power Query with a newly downloaded file of the same name without opening it first.
- Observed behavior
- Power Query truncates the data load, importing only 255 columns instead of the full 451 columns present in the source file.
Before applying these fixes, ensure you have a backup of your original dataset and verify that the newly downloaded source file actually contains all the expected columns.
Open, Enable Editing, and Save the Replacement File
Files downloaded from the internet are typically locked in Protected View, which restricts Power Query from properly reading all file metadata and columns.
When you download a replacement file from the internet, Microsoft Excel automatically places it in Protected View for security reasons. If you attempt to use this file directly as a Power Query source by just renaming it, the query engine may revert to legacy limitations (like the 255-column limit) because it cannot fully access the workbook's internal XML structure. Initializing the file by opening and saving it removes this barrier.
Navigate to the folder containing your newly downloaded replacement XLSX file and open it in Excel.
Look for the yellow Protected View warning banner at the top of the screen and click the 'Enable Editing' button.
Press Ctrl + S or go to File > Save to apply the changes and initialize the file's structure. You can then close the file.
Open your main workbook containing the Power Query connection, go to the Data tab on the ribbon, and click 'Refresh All'.
Create a New Workbook and Update Microsoft 365
If the existing Power Query connection is corrupted or encountering a software bug, rebuilding the connection in a new file and updating Excel can resolve the issue.
Handle Complex Datasets with WPS Office
If Microsoft Excel's Power Query limits and Protected View issues are frequently disrupting your workflow, consider switching to WPS Office. It provides a highly compatible, robust environment for managing massive datasets without unnecessary feature blocks.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your device in minutes.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your downloaded .xlsx or .csv dataset directly without complex import steps.
- 3. Analyze and Process Data: Use built-in data processing tools to sort, filter, and analyze hundreds of columns without arbitrary limitations.
Frequently Asked Questions
Why does Power Query stop exactly at 255 columns?
Historically, older Excel formats (.xls) had a hard structural limit of 255 columns. When Power Query encounters a file locked in Protected View or lacking proper metadata initialization, it may mistakenly process the file using legacy drivers, triggering the 255-column cutoff.
Do I always have to open and save downloaded files before using Power Query?
Yes, files downloaded from the internet usually open in Protected View to prevent malicious code execution. Power Query cannot always read the complete internal structure of a file in Protected View, resulting in truncated columns or import errors. Saving the file removes this restriction.
How can I bypass the 255-column limit when importing CSV files?
When importing CSV or Text files, ensure the delimiter is set correctly and the file encoding is recognized in the Power Query Editor. Because text files do not carry the legacy XLS structural limitations, importing them directly usually loads all columns successfully.
Can I automate the process of enabling editing and saving?
Yes. You can use VBA macros or Power Automate Desktop scripts to automatically open, save, and close downloaded files in the background. This removes the Protected View restriction automatically before Power Query attempts to refresh the data.
