Power Query Problems

How to Fix Power Query Append Creating an Extra Column in Excel

Kushani Nimanthika Oct 15, 2026 870 views

Question details

The user experiences an issue where appending tables in Power Query results in an unexpected extra column, despite the source tables appearing to have identical structures.

How to Fix Power Query Append Creating an Extra Column

Product: Excel / Power Query
Device & OS: not provided
Scenario: Appending multiple tables or data sources using Power Query.
Observed behavior: An extra column is generated in the appended query result due to a mismatch in column headers that is not visually obvious.

Before you start

Before modifying your queries, ensure you have loaded a preview of all source tables in the Power Query Editor to easily verify their current column headers.

Solution 1Recommended

Remove Hidden Trailing Spaces in Column Headers

Identify and rename mismatched column headers that contain invisible characters like trailing spaces.

Power Query is case-sensitive and strictly matches column names when appending tables. Even a single hidden trailing space (for example, 'Title ' instead of 'Title') will cause Power Query to treat them as two distinct columns. This results in the data splitting into an extra column in your appended output instead of aligning properly.

Inspect Column Headers

Open the Power Query Editor and select each source query from the left pane. Locate the header of the column that is splitting into two in your append results.

Check for Hidden Spaces

Double-click the column header to edit the text. Click at the very end of the text and look closely for a trailing space at the end of the column name.

Rename to Match Exactly

Delete the trailing space or rename the column so it exactly matches the header used in your other tables. Press Enter to apply the change.

Refresh the Append

Navigate to your appended query. The extra column should now be merged properly into a single column. Click 'Close & Load' on the Home tab to apply the changes to your worksheet.

Remove Hidden Trailing Spaces in Column Headers

Pro Tip: To prevent this in the future without checking manually, you can use the 'Table.TransformColumnNames(Source, Text.Trim)' function in the Advanced Editor to strip leading and trailing spaces from all headers automatically.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

While Power Query is a powerful tool in Microsoft Excel, issues like hidden trailing spaces and strict case sensitivity can sometimes complicate data consolidation. If you are looking for a highly compatible, cost-effective alternative for daily data processing and analysis, WPS Office offers robust spreadsheet capabilities with a familiar, easy-to-use interface.

1. Download and Install: Visit the official WPS Office website to download the free suite for your device.
2. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel workbooks seamlessly without format loss.
3. Manage Data Intuitively: Use WPS Office's intuitive built-in data tools to consolidate, merge, and organize your tables easily.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Familiar user interface requiring zero learning curve to start managing data.Lightweight application that runs smoothly even on older devices.Powerful built-in data processing, sorting, and filtering tools for easy table management.

microsoft office alternative - wps office

Frequently Asked Questions

Why is Power Query appending my data into separate columns instead of one?

Power Query strictly checks string matches for column headers. If one table has the header "Sales" and another has "sales " (with a trailing space), it treats them as entirely different columns and places them side-by-side during an append.

How can I automatically remove trailing spaces from column headers in Power Query?

You can clean headers by going to the Advanced Editor and wrapping your source step in the Table.TransformColumnNames function combined with Text.Trim. This strips leading and trailing spaces from all headers simultaneously.

Does case sensitivity affect appending tables in Power Query?

Yes, Power Query is fully case-sensitive. A column named "Amount" will not align with a column named "amount". You must rename the columns to match exactly, or use Text.Proper/Text.Lower within your transformation steps to standardize them before appending.

Why don't trailing spaces show up in standard Excel tables?

Excel allows trailing spaces in headers and often masks them visually in regular worksheets. However, when imported into a strict data transformation environment like Power Query, these hidden characters become significant and directly affect operations like merges and appends.

Excel & Data Problems