How to Merge Two Excel Tables Without Losing Unmatched Rows in WPS Office
Question details
The user needs to combine two Excel tables in WPS Office using Power Query while keeping all rows, including records that appear in only one table, and leaving missing fields blank.
- Product
- WPS Office Excel / Power Query
- Device & OS
- not provided
- Scenario
- The user is consolidating two related tables that share key identifiers but contain different sets of records.
- Observed behavior
- A regular merge can drop unmatched records; the desired result is a combined table that preserves unmatched rows with blank or null values.
Make sure both tables are in a WPS Office Excel workbook and each has a key column with matching data types, such as ID numbers formatted the same way in both tables.
Use a Full Outer Join in WPS Power Query to Keep Unmatched Rows
Use this method when you need one consolidated table and cannot afford to lose records that do not have a match in the other table.
A Full Outer join keeps rows from both tables. When a key exists in only one table, only that table's columns are populated and the other columns remain blank or null.
WPS Office Excel uses the same Power Query Merge Queries workflow, so you can also paste the Full Outer join M expression into the Advanced Editor if you prefer.
Open the workbook in WPS Office Excel, click a cell inside the first table, go to Data > From Range/Table or Get Data > From Table/Range, and repeat this for the second table.
In Power Query Editor, click Home > Merge Queries. Select Table1 and Table2, choose the ID key columns in both tables, and set Join Kind to Full Outer.
Click the expand icon in the merged column header, select the columns you want to add, and click OK.
Use Add Column > Custom Column to create an ID from nonblank values, or open View > Advanced Editor and use the Full Outer join pattern: Table.NestedJoin(Table1,{"ID1"},Table2,{"ID2"},"Table2",JoinKind.FullOuter). Then remove duplicate key columns and sort by ID.
Merge Excel Tables in WPS Office with Power Query Without Dropping Unmatched Rows
Use WPS Office Excel Power Query to merge two tables with a Full Outer join, preserve unmatched rows, and keep missing values blank while maintaining Excel compatibility.
- 1. Download WPS Office: Visit the WPS Office download page and install WPS Office free on your computer.
- 2. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the Excel workbook containing the two tables, and keep the .xlsx format.
- 3. Load the tables into Power Query: Select each table, go to Data > From Range/Table or Get Data > From Table/Range to load both tables into Power Query Editor.
- 4. Merge with Full Outer join: In Power Query Editor, go to Home > Merge Queries, select the matching key columns, choose Full Outer as the Join Kind, expand the second table, and apply the query.
Frequently Asked Questions
What join type should I use to keep unmatched rows when merging two Excel tables in WPS Office?
Use Full Outer join in Power Query. It keeps all rows from both tables, and rows that do not have a match in the other table appear with null or blank values in the unmatched columns.
Why do I see blank cells after merging two Excel tables with Power Query?
Blank or null cells indicate that a row exists in only one of the source tables. You can leave them blank or replace null with a custom value using Power Query Replace Values.
Can I merge two tables if the key columns have different names?
Yes. In the Merge Queries dialog, select the first key column from the first table and the matching key column from the second table; the column names do not need to be identical.
How do I avoid duplicate ID columns after a Full Outer merge in WPS Office?
Create a combined ID column that takes the first non-null value from either key column, then remove the original key columns and keep only the combined ID.
Does WPS Office support Power Query Merge Queries?
Yes. WPS Office Excel includes Power Query features, so you can use the Merge Queries dialog or Advanced Editor in WPS Spreadsheets.
