Power Query Problems

How to Add Data from Another Worksheet Using Power Query

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to know how to use Power Query to match names from one worksheet with another table in order to add a corresponding column (such as salary data) to their main dataset.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Merging data from two separate worksheets or tables based on a common matching identifier, similar to a VLOOKUP operation.
Observed behavior: The user needs a structured way to establish a relationship between a player's name and their salary stored in a different location, filling the matched results appropriately for each row.

Before you start

Ensure both sets of data are formatted as Excel Tables (press Ctrl+T) and that the columns containing the matching identifiers (e.g., player names) have exactly the same data type and text formatting to prevent merging errors.

Solution 1Recommended

Merge Queries to Add Column Data

Load both tables into Power Query and use the Merge Queries feature to join them based on a matching column.

Power Query provides a visual interface for combining data from multiple sources. By merging queries, you can easily pull in corresponding data from a lookup table without writing complex formulas.

Load the primary table

Select any cell in your main data table, go to the 'Data' tab on the Excel ribbon, and click 'From Table/Range' to load it into the Power Query Editor.

Load the lookup table

Close the editor temporarily by choosing 'Close & Load To...' and creating a Connection Only. Repeat the previous step for your lookup table (the one containing the salary data), so both tables are now available in Power Query.

Initiate the merge

Open the primary query in the Power Query Editor, navigate to the 'Home' tab, and click the 'Merge Queries' button.

Select matching columns

In the Merge dialog box, select your lookup table from the dropdown menu. Click on the matching columns (e.g., 'Player Name') in both the top and bottom table previews to link them, then click 'OK'.

Expand the required field

A new column containing 'Table' objects will appear. Click the expand icon (two diverging arrows) in the column header, select only the 'Salary' field, uncheck 'Use original column name as prefix', and click 'OK'.

Load the combined data

Click 'Close & Load' on the Home tab to output your newly combined dataset back into a fresh Excel worksheet.

Data Connection: By creating connections instead of duplicating data, any future updates to your original salary worksheet will automatically reflect in your final merged table when you hit Refresh.

Free Microsoft Office alternative

Easily Combine Data and Match Columns in WPS Office

While Power Query is a powerful tool in Microsoft Excel, merging tables and looking up values can sometimes feel overly complex for simple tasks. WPS Office Spreadsheet offers a lightweight, intuitive interface with robust built-in functions to easily combine data from multiple worksheets for free.

1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheet and open the file containing both your primary dataset and the lookup worksheet.
2. Use a Lookup function: In the empty Salary column of your main sheet, type '=VLOOKUP(' or '=XLOOKUP(' to quickly pull data from the secondary sheet based on matching names.
3. Drag to fill: Select the completed formula cell and double-click the fill handle in the bottom-right corner to instantly populate the salary data for all rows.

Highly compatible with Microsoft Excel (.xlsx) formats, formulas, and table structures.Intuitive formula builder for quick VLOOKUP and XLOOKUP data matching without heavy add-ins.Lightweight installation with fast performance, even on large datasets.Completely free to download and use for your daily spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a data format error when merging queries in Power Query?

This usually happens when the matching columns have different data types. For example, if one 'Player Name' column is formatted as Text and the other as General or Any, Power Query may fail to match them. Ensure both columns are explicitly set to the 'Text' data type in the editor.

Can I merge more than two worksheets using Power Query?

Yes, you can merge multiple tables sequentially. Once you merge the first two queries and expand the necessary columns, you can simply click 'Merge Queries' again to join a third table to your current working query.

Why are some rows showing 'null' after I expand my merged column?

A 'null' value indicates that Power Query could not find an exact matching record in the lookup table. Check for trailing spaces, spelling differences, or case sensitivity issues between the player names in both tables.

Is there a simpler way to add a salary column without using Power Query?

Yes. If you only need to pull in a single column from another worksheet and do not require complex data transformations, using functions like VLOOKUP or XLOOKUP directly in your spreadsheet is often faster and doesn't require loading data into the Power Query Editor.

Excel & Data Problems