How to Prevent Power Query from Removing a Blank First Column in Excel
Question details
Users need a way to stop Power Query from automatically dropping blank columns before the first populated column when combining files.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Combining multiple CSV or Excel files where some files have an entirely blank first column, causing data misalignment.
- Observed behavior
- Power Query drops the blank first column during combination, shifting subsequent column names to the left and causing data to misalign when files are appended.
Verify the exact number of expected columns across all your source files and open the Advanced Editor in Power Query to prepare for M-code adjustments.
Use Advanced Editor to Dynamically Adjust Column Names
Modify the Transform Sample File query using M code to detect missing columns and shift column names accordingly to ensure alignment.
By default, Power Query's Csv.Document function relies on the first sample file to set options, including the number of columns. If a subsequent file has a blank first column, Power Query drops it, shifting all data to the left.
You can adjust the M code to evaluate the column count dynamically. If a file has fewer visible columns, the query will shift the column names so that everything aligns correctly during the final combination.
In the Power Query Editor, locate the 'Transform Sample File' query in the left Queries pane, right-click it, and select 'Advanced Editor'.
Find the 'Source = Csv.Document(...)' line and remove the 'Columns=X' parameter so it does not strictly force the sample file's column count on every subsequent file.
Define your expected number of columns (e.g., NumberOfColumns = 4) and use 'Table.ColumnCount' to check the incoming file. Create a condition: if the count is less than expected, use 'Table.RenameColumns' to shift the names (e.g., renaming Column 1 to Column 2).
Click 'Done' in the Advanced Editor. The appended query will now align files by their adjusted column names instead of simply stacking shifted data.
Create Separate Queries and Append Manually
Process files with different column structures in separate queries, standardize them individually, and then append the queries.
Edit and Manage Your Data Easily with WPS Office
While complex M-code scripts and Power Query functionalities are native to Microsoft Excel, WPS Office offers a free, lightweight, and highly compatible alternative for standard data processing, filtering, and spreadsheet management.
Frequently Asked Questions
Why does Power Query remove blank columns automatically?
Power Query attempts to optimize data by removing columns that appear entirely empty. When combining files, it relies on the first sample file's structure. If subsequent files differ or have leading blank columns, the automatic detection fails to align them correctly.
Can I fix column alignment without writing M code?
Yes. You can load your files into separate individual queries, manually add or delete columns so that all queries have the exact same structure and column names, and then use the 'Append Queries' feature to combine them.
How do I specify the expected number of columns in Csv.Document?
You can open the Advanced Editor and manually add or adjust the 'Columns' parameter inside the Csv.Document function. For example: Csv.Document(Source, [Delimiter=",", Columns=5, Encoding=65001]).
