How to Prevent Power Query from Dropping Columns in Combined Files
Question details
Users need to ensure all columns are retained when combining multiple files with varying structures in Power Query.
- Product
- Excel / Power Query
- Device & OS
- not provided
- Scenario
- Combining multiple CSV or PDF files from a folder using Power Query where the source files have inconsistent column structures.
- Observed behavior
- Power Query drops or omits columns that exist in some files but not in the sample file, due to a fixed schema limit.
Ensure you have a backup of your original query and check the structures of your source files to identify the maximum number of columns you actually need to import.
Adjust the Csv.Document Parameter in the Sample File Query
The most common reason for dropped columns in CSV files is a hardcoded column limit generated in the sample file query.
When Power Query combines files, it creates a 'Transform Sample File' query based on the first file it reads. If this file has fewer columns than others, Power Query hardcodes this limit, causing wider files to be truncated.
In the Power Query Editor, go to the Queries pane on the left, expand the 'Helper Queries' folder, and select the 'Transform Sample File' query.
Click on the 'Source' step in the Applied Steps pane to view the Csv.Document formula in the formula bar.
Look for the 'Columns=X' parameter (e.g., Columns=10). Either remove this parameter completely or increase the number to safely cover the maximum possible columns in your files.
Review the generated sample-file steps. Ensure that later steps (like 'Changed Type') are not locking the query into selecting only the original subset of columns.
Use Table.Combine for Varying Schemas
If your files have drastically different schemas, you can manually combine the tables to ensure no fields are lost and missing fields are handled properly.
Try WPS Office for Seamless Data Management
While complex Power Query issues often require deep troubleshooting in Excel, WPS Office offers a lightweight, highly compatible alternative for everyday data analysis and spreadsheet management.
- 1. Download WPS Office: Visit the official WPS website and download the free version for your operating system.
- 2. Install and Launch: Follow the simple installation prompts and open WPS Spreadsheet.
- 3. Open Your Data: Easily open your .xlsx or .csv files to manage your data with highly compatible tools.
Frequently Asked Questions
Why does Power Query drop columns when combining PDFs?
PDF imports rely on structural recognition. If tables across PDF pages or files differ in layout, Power Query may lock onto the first file's schema. You may need to use connector-specific settings or build a custom transformation function to handle varying PDF tables.
Can I make Power Query automatically detect all columns without specifying a number?
Yes, for CSV files, if you remove the 'Columns' parameter entirely from the Csv.Document function in the Advanced Editor, Power Query will attempt to dynamically detect the number of columns based on the file's contents.
What happens to the missing data when using Table.Combine?
When you use Table.Combine on tables with different column structures, Power Query includes all unique columns from all tables. For rows where a specific column did not exist in the source file, it will display a 'null' value.
Why did fixing the Source step break my 'Changed Type' step?
The 'Changed Type' step hardcodes column names. If you remove the column limit, new columns are added that aren't defined in the 'Changed Type' step, or previous column names shift. You must delete or update the 'Changed Type' step to reflect the dynamic columns.
