Power Query Problems

How to Combine Data from Multiple Workbooks with Different Headings in Power Query

Olivia Miller Sep 25, 2026 869 views

Question details

The user needs to consolidate data from multiple workbooks that contain different column headings and fields.

How to Combine Data from Multiple Workbooks with Different Fields in Power Query

Product: Excel Power Query
Device & OS: not provided
Scenario: Attempting to merge datasets from several files where the column structures, such as Plant, Registration Number, and Depot, do not perfectly match.
Observed behavior: The tables cannot be straightforwardly appended due to differing headings, requiring the establishment of a clear relationship between the datasets to join them correctly.

Before you start

Before beginning, ensure that all workbooks are saved locally or in an accessible folder, and identify at least one common column (like an ID or Registration Number) shared across the files.

Solution 1Recommended

Use Merge Queries to Link Datasets via a Common Key

Use this method when your tables have different fields but share a unique identifier that can be used to join the data accurately.

When workbooks do not share the exact same column headers, using the standard append feature will misalign your data or create null columns. Instead, you must establish a relationship using a common key to merge the queries.

Import the Workbooks

Navigate to the Data tab in Excel, select 'Get Data', choose 'From File', and then 'From Workbook' to load your datasets into the Power Query Editor.

Identify the Common Key

Examine both queries in the editor and locate a unique identifier present in both datasets, such as 'Registration Number' or 'Vehicle ID'.

Initiate the Merge

On the Home tab of the Power Query Editor, click on 'Merge Queries'. In the dialog box, select your primary query at the top and your secondary query from the drop-down menu.

Select Matching Columns

Click on the column containing the common key in both preview windows to highlight them, select the appropriate Join Kind (e.g., Left Outer), and click OK to combine the data.

Use Merge Queries to Link Datasets via a Common Key

Protecting Sensitive Data: If you plan to share your combined workbooks or provide sample files, make sure to remove or mask any private information before finalizing the merge.

Free Microsoft Office alternative

A Lightweight and Compatible Alternative for Data Analysis

If you are struggling with complex data combinations or encountering limitations in your current spreadsheet tool, try WPS Office. It provides a free, lightweight spreadsheet solution with robust data tools, a familiar interface, and seamless migration for your Excel files.

1. Download and Install: Get WPS Office for free from the official website and install it onto your computer.
2. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx files directly without any formatting loss.
3. Consolidate Your Data: Utilize features like Data Consolidation and Pivot Tables under the Data tab to easily merge and analyze your datasets.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Powerful built-in data tools for filtering, sorting, and consolidating multiple sheets.Familiar user interface that allows you to seamlessly migrate without a steep learning curve.Free and lightweight, consuming minimal system resources for fast performance.

Frequently Asked Questions

Can I append tables in Power Query if the column headers do not match exactly?

Yes, but Power Query will create separate columns for any headers that do not match exactly, filling the gaps with null values. To properly align the data, you should rename the headers so they match before appending, or use Merge Queries if the tables represent different attributes of the same entities.

What is considered a common key in Power Query?

A common key is a column or set of columns that contain unique identifiers (such as Employee ID, Vehicle ID, or Invoice Number) present in multiple tables. This key allows Power Query to accurately link corresponding rows between different datasets.

How do I remove private information from a workbook in Power Query?

You can remove sensitive data within the Power Query Editor before loading the data into your spreadsheet. Simply right-click the column header containing the private information and select 'Remove' from the context menu.

Excel & Data Problems