Fix Power Query Importing Only One Column from Excel Files
Question details
Users experience an issue where Power Query fails to load all data, displaying only the very first column when expanding or importing an Excel file.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing or expanding multiple Excel workbooks from a folder using Power Query.
- Observed behavior
- Only the first column of the Excel file is imported because the worksheet dimension metadata incorrectly identifies the used range, typically when files are generated by external programs.
Verify if the affected Excel files were automatically generated or exported by a third-party CRM, database, or software, as these tools often write incomplete worksheet dimension metadata.
Open and Resave the Affected Excel Files
The most straightforward solution is to let Excel automatically recalculate the correct worksheet dimensions by manually opening and saving the file.
When third-party applications export data to an Excel format, they sometimes fail to update the internal XML metadata that defines the 'used range' of the sheet. Opening the file in Excel forces the application to rebuild this metadata properly.
Double-click the problematic Excel file from your folder to open it in Microsoft Excel.
Press Ctrl + S to save the file, or go to File > Save. You can also use 'Save As' to create a fresh copy.
Return to your main workbook, open the Data tab, and click 'Refresh All' to see if Power Query now detects all columns.
Modify M Code with InferSheetDimensions
Update your query's M code to force Power Query to infer the sheet dimensions directly rather than relying on the file's potentially flawed metadata.
Change the Export Format in the Source Program
Prevent the issue entirely by exporting your source data in a format that does not rely on Excel worksheet dimensions, such as CSV.
Try WPS Office for Seamless Data Management
If you are frustrated by metadata errors, complex Power Query troubleshooting, and compatibility issues in Microsoft Excel, consider trying WPS Office. It offers a fast, lightweight spreadsheet tool that easily opens files from third-party systems without getting hung up on strict dimensional metadata.
- 1. Download WPS Office: Visit the official WPS website and download the free version of WPS Office.
- 2. Install and Launch: Follow the simple installation prompt, then open WPS Spreadsheet.
- 3. Open Your Data File: Go to Menu > Open and select your exported data file to view all columns perfectly intact.
Frequently Asked Questions
Why does Power Query think my Excel file only has one column?
This usually happens when the Excel file was generated by an external system or third-party software. These programs sometimes write incorrect 'used range' metadata into the file, causing Power Query to believe the data stops after the first column.
Is InferSheetDimensions supported in all versions of Excel?
The InferSheetDimensions parameter for the Excel.Workbook function is supported in modern versions of Excel (Microsoft 365) and Power BI. If you are using an older version of Excel, you may need to rely on the open-and-resave method instead.
Will converting the file to CSV fix the Power Query missing columns issue?
Yes. CSV files are plain text files that do not contain hidden worksheet dimension metadata. When Power Query imports a CSV, it parses the data based purely on the delimiters (like commas), ensuring all columns are captured.
