Power Query Problems

How to Append Power Query Tables with Different Column Headers in Excel

WPS Content Manager Oct 10, 2026 869 views

Question details

The user needs to append two Power Query tables derived from PDFs into a single table, but they have different column headers (one has an extra 'Type' column).

How to Append Power Query Tables with Different Column Headers

Product: Microsoft Excel (Power Query)
Device & OS: not provided
Scenario: Combining data from multiple PDF-derived tables where the columns do not perfectly match across datasets.
Observed behavior: The user wants to combine the tables, but the presence of an extra column prevents a clean direct append without data misalignment or null values.

Before you start

Before appending your tables, ensure both queries are loaded in the Power Query Editor and carefully identify which specific columns differ between the two datasets.

Solution 1Recommended

Align Column Headers and Append Queries

Power Query appends tables by matching column names exactly. You must remove unnecessary columns and ensure the remaining headers match perfectly before combining.

When appending queries, Power Query automatically aligns columns that share the exact same header name. Columns that only exist in one table will be added to the final combined table, populated with null values for the records from the other table.

Promote First Row to Headers

In both queries, check if your column names are currently in the first data row. If so, go to the Home tab and select 'Use First Row as Headers' to correctly establish the columns.

Remove Extra Columns

Select the query with the extra column (e.g., the 'Type' column). Right-click the column header and select 'Remove' to align its structure with the other table.

Verify Column Names

Check that the remaining column headers in your first table have the exact same spelling, capitalization, and spacing as those in your second table.

Append the Tables

Go to the Home tab and click on 'Append Queries' (or 'Append Queries as New'). In the dialog box, select your two tables from the dropdowns, then click OK.

Case Sensitivity in Power Query: Power Query is strictly case-sensitive. Headers named 'Sales' and 'sales' will be treated as two completely different columns during the append process.

Free Microsoft Office alternative

Handle Your Spreadsheet Data Easily with WPS Office

While complex Power Query operations are specific to Microsoft Excel, WPS Office provides a free, lightweight, and highly compatible alternative for everyday data analysis, combining sheets, and managing your spreadsheets without expensive subscription costs.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Built-in intuitive tools for data consolidation and pivot tables.Lightweight software that runs smoothly even on older devices.Familiar user interface for a seamless migration from Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

What happens if I don't remove the extra column before appending in Power Query?

If you do not remove the extra column, Power Query will still append the tables successfully. However, it will create a new column in the combined table for the unmatched header, filling it with 'null' values for all rows coming from the table that lacked that column originally.

Does column order matter when appending tables in Power Query?

No, column order does not matter. Power Query appends data by matching the exact column names, not their position. As long as the header names match perfectly across both tables, the data will align correctly in the final output.

Why are my matching columns still appearing separately after appending?

This usually happens due to mismatched text formatting. Power Query is case-sensitive, so 'Type' and 'type' are treated as separate columns. Extra trailing or leading spaces in the header name can also cause this issue. Ensure the headers are exactly identical in both spelling and casing.

Excel & Data Problems