Handling “Extra Columns” that appear unexpectedly in Folder Imports.

Unexpected extra columns (such as Column11, Column12, or duplicated column names) appear during Power Query folder imports when one or more files in the folder deviate from the sample file schema. This problem occurs when individual CSV or Excel files have different column headers, trailing delimiters, extra notes placed outside the data grid, or open temporary files (~$) that introduce phantom data into the folder consolidation process.

Quick Answer

To eliminate unexpected columns, filter out temporary files (files beginning with ~$) immediately after the Source step. Next, open the Transform Sample File query, use Choose Columns to keep only the required fields, and remove any hardcoded Columns=X parameters inside the Csv.Document step in the Advanced Editor.

---

Why Folder Imports Produce Extra Columns

When you import a folder using Folder.Files or Folder.Contents, Power Query builds an automated consolidation pipeline:

1. It selects the first available file as a Sample File.
2. It generates a helper query named Transform Sample File.
3. It applies those transformation steps across every file in the target folder.
4. It merges all outputs using Table.Combine.

This setup is vulnerable to schema drift. If even one file in the directory has an extra column, a different header spelling, or an extra comma at the end of a line, Table.Combine cannot line up the rows cleanly. Instead of dropping the mismatched data, it appends the new fields to the far right side of your table as new columns.

---

Step-by-Step Troubleshooting and Prevention

Step 1: Filter Out Temporary Office Lock Files

Whenever an Excel file is open, Microsoft Office creates a hidden temporary lock file in the same directory. These files start with a tilde and dollar sign (~$filename.xlsx).

If Power Query reads this temporary file, it attempts to parse lock metadata as if it were a valid data file, creating misaligned columns and empty fields.

1. In the Applied Steps pane of your main query, select the Source step.
2. In the file list, click the filter dropdown arrow on the Name column.
3. Select Text Filters > Does Not Begin With.
4. Type ~$ into the box and click OK.

For a detailed review of lock-file errors and access issues, see Fixing errors in Folder.Files when a temp file (~$) is open in the folder.

Step 2: Remove the Hardcoded Column Count in CSV Imports

When importing CSV files, Power Query frequently hardcodes the number of columns it expects based on the sample file. In the Advanced Editor, you will often find an expression like this:

Csv.Document(..., [Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None])

If another file in the folder contains 12 columns, or if a comment is added to the right of the data, the fixed parameter Columns=10 forces Power Query to split data unpredictably, shifting fields into unexpected columns.

1. In the left-hand Queries pane, select the Transform Sample File query.
2. Go to the Home tab and click Advanced Editor.
3. Locate the Csv.Document step.
4. Delete the parameter Columns=X, entirely from the function call:

// Before:
Source = Csv.Document(Parameter1, [Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None])

// After:
Source = Csv.Document(Parameter1, [Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None])

1. Click Done.

Power Query will now read each file based on its actual structure rather than forcing a rigid column count.

Step 3: Enforce Schema Using “Choose Columns” on the Sample Query

Rather than cleaning up the combined table after all files have merged, enforce your target structure inside the Transform Sample File helper query. This ensures that every file is trimmed to the exact same shape before the combine step occurs.

1. Under the Helper Queries group in the left-hand pane, click Transform Sample File.
2. Go to the Home tab and click Choose Columns.
3. Uncheck all fields except the specific columns your report requires.
4. Click OK.

Power Query writes a Table.SelectColumns step. If any file contains extra notes, trailing delimiters, or phantom columns, they are dropped immediately.

To learn how to manage and modify these sample transformation steps, see How to use “Transform File” to fix errors across multiple CSVs in a folder.

Step 4: Handle Column Header Spacing and Capitalization

If an extra column contains data that should belong to an existing column (for example, you see both InvoiceDate and Invoice Date), your files suffer from header mismatch rather than extra fields.

Because Table.Combine is case-sensitive and sensitive to spaces, any spelling difference forces the engine to create a new column.

If this is happening in your files, you need to normalize headers before the merge step. For step-by-step techniques to handle shifting column names during appends, refer to Fixing “Column Name Mismatch” when appending two tables.

---

What to Do If Trailing Delimiters Keep Adding Blank Columns

In many automated system exports, rows end with a trailing comma or delimiter (such as 101,John,Smith,). Power Query reads the delimiter and creates an unnamed, empty column on the right side of the table (often named Column4 or _1).

To fix this systematically across all files:

1. In the Transform Sample File query, inspect the final column.
2. If the column contains only nulls or blank values across every row, select the column, right-click its header, and select Remove.
3. Power Query adds Table.RemoveColumns to the helper pipeline, stripping the trailing empty column from every imported file before merging.