Power Query Problems

Fix Excel Power Query Folder Combine Failing with Changed Columns

Steve K Oct 12, 2026 870 views

Question details

The user needs a way to prevent Excel Power Query from breaking when combining CSV files from a folder where the source data structure changes over time, such as added columns or changed column orders.

How to Fix Power Query Folder Combine Fails When Columns Change

Product: Microsoft Excel
Device & OS: not provided
Scenario: Combining multiple CSV files from a folder using Power Query, where newer files contain structural changes like new columns or reordered fields.
Observed behavior: The folder combine process fails and throws an error because the query relies on fixed column positions or hard-coded column names that no longer match the incoming files.

Before you start

Before modifying your query steps, make a backup copy of your Excel workbook and ensure all your target CSV files are accessible in the designated folder without permission restrictions.

Solution 1Recommended

Remove Hard-Coded Column Steps in the Sample Transformation

Adjust the sample-file transformation query to remove steps that lock in specific column positions or names, allowing Power Query to dynamically read changing structures.

When Power Query combines files, it generates a 'Transform Sample File' query. If this query includes automatic steps like 'Changed Type' or 'Reordered Columns', it writes the exact column names and positions into the M code. Removing these rigid steps ensures the query handles new or reordered columns seamlessly.

Open Power Query Editor

In Excel, navigate to the Data tab, click on 'Get Data', and select 'Launch Power Query Editor'.

Locate the Sample File Query

In the Queries pane on the left, expand the 'Transform File from [Folder Name]' folder and select the 'Transform Sample File' query.

Delete Rigid Applied Steps

Look at the 'Applied Steps' pane on the right side. Right-click and delete any steps labeled 'Changed Type', 'Reordered Columns', or 'Removed Columns' that specifically reference the old column names.

Review Main Combine Query

Select your main combined query from the left pane. Check its 'Expanded Table Column' step to ensure it is dynamically selecting columns instead of relying on a hard-coded list of missing fields.

Remove Hard-Coded Column Steps in the Sample Transformation

Pro Tip: You can view the exact M code by clicking 'Advanced Editor' on the Home tab. This helps you quickly spot and delete arrays containing hard-coded column names (e.g., {"Column1", "Column2"}).

Solution 2

Re-import Files Using the Clean Combine Feature

Start a fresh folder combine process while actively avoiding the automatic type detection that causes hard-coded errors.

Free Microsoft Office alternative

Use WPS Office for Seamless Data Management

While Power Query errors in Excel can be complex to troubleshoot, WPS Office offers a free, lightweight, and highly compatible alternative for everyday data analysis and spreadsheet tasks.

1. Download and Install: Visit the official WPS website to download and install the free WPS Office suite on your device.
2. Open WPS Spreadsheet: Launch WPS Office and open the Spreadsheet module to start a new workbook.
3. Import Your Data: Go to the Data tab to easily import your CSV files and use built-in tools to merge and manage your data.

Highly compatible with Microsoft Excel formats including .xlsx, .xls, and .csvLightweight application that runs smoothly on Windows, Mac, Linux, and mobile devicesFamiliar user interface allowing for quick adoption without a steep learning curveRobust built-in data analysis tools to sort, filter, and summarize spreadsheet data easily

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query fail when the column order changes in my CSV files?

Power Query often records automatic transformation steps, such as 'Changed Type' or 'Reordered Columns', using a hard-coded list of column names and their exact positions. When newer CSV files have a different column order or new columns, these rigid steps can no longer find the expected fields, causing an error.

How do I view the Power Query M code to find hard-coded columns?

Inside the Power Query Editor, go to the 'Home' tab on the ribbon and click on 'Advanced Editor'. This will open a text window showing the underlying M code for your query, where you can easily spot and remove specific arrays of hard-coded column names.

Can Power Query combine files that have completely different columns?

Yes, Power Query is capable of appending files with different column structures. It will dynamically union the column names, creating new columns for fields that don't match across files and filling the empty spaces with null values. You simply need to ensure your query doesn't restrict the expansion to a fixed list of old columns.

Excel & Data Problems