How to Combine Sheets and Fill Blank Cells using Power Query
Question details
The user needs to merge two large Excel tables based on a shared key and replace any blank cells in the primary table with corresponding non-blank values from the secondary table.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing and consolidating a database export of over 100,000 records keyed by identifiers like email addresses, where missing data must be filled without manual effort.
- Observed behavior
- The user requires an automated method using Power Query to accurately join datasets and conditionally output data based on whether a cell is null or blank.
Before beginning, ensure both tables share a common, consistently formatted key column (such as an Email Address or ID). It is highly recommended to test your merge logic on a small, sanitized sample of your data to verify the results before processing all 100,000+ records.
Use Power Query to Merge Tables and Add Conditional Columns
This solution leverages Excel's Get & Transform (Power Query) features to join the datasets and intelligently fill missing values using a conditional column.
Power Query is robust enough to process over 100,000 records efficiently without bogging down your workbook. By importing both tables and using a Left Outer Join on the shared key, you can bring the secondary data into your primary dataset.
Empty cells in Excel often appear as 'null' in Power Query. You will use a Conditional Column to evaluate if the primary value is null, and if so, fetch the value from the secondary table.
Navigate to the 'Data' tab on the Excel ribbon. Click 'Get Data' > 'From Table/Range' to import your first table into the Power Query Editor. Repeat this process for the second table.
In the Power Query Editor, select your primary table. Click 'Merge Queries' on the Home tab. Select your secondary table from the dropdown, then click on the shared key column (e.g., Email Address) in both table previews to link them.
A new column containing 'Table' values will appear. Click the expand icon (two diverging arrows) at the top of this column header, uncheck 'Use original column name as prefix', and select the specific column containing the fallback values.
Go to the 'Add Column' tab and select 'Conditional Column'. Name the new column. Set the logic to: If [Primary Column] equals 'null', then output [Secondary Column], otherwise output [Primary Column].
Right-click and 'Remove' the original primary column and the expanded secondary column, keeping only your newly created conditional column. Finally, go to the Home tab, click 'Close & Load', and return your merged dataset to Excel.
Merge Sheets and Fill Blanks easily in WPS Spreadsheet
If you need to quickly consolidate large tables and fill blank values without learning the intricacies of Power Query, WPS Spreadsheet offers an intuitive interface with powerful functions like VLOOKUP and IF to handle extensive datasets smoothly.
- 1. Open your datasets in WPS Spreadsheet: Launch WPS Office and open the workbook that contains both of your large data tables.
- 2. Write a conditional lookup formula: Insert a new column next to your target data. Use a combination formula such as =IF(ISBLANK(B2), VLOOKUP(A2, Sheet2!A:C, 2, FALSE), B2) to pull in data only when the original cell is empty.
- 3. Apply across the entire dataset: Double-click the fill handle at the bottom-right corner of the formula cell to instantly apply the logic to all 100,000+ records in the table.
- 4. Convert formulas to values: Select the newly filled column, press Ctrl+C to copy, then right-click and choose 'Paste as Values' to remove the formulas and finalize your cleaned data.
Frequently Asked Questions
Why does Power Query show 'null' instead of empty blank cells?
When Power Query imports data from Excel, it translates completely empty cells into 'null' values. When writing conditional formulas or creating conditional columns, you must explicitly check for 'null' rather than an empty text string ("").
Will merging 100,000 records crash my spreadsheet?
Power Query is explicitly designed to handle millions of rows, significantly outperforming standard Excel formulas. However, processing speed will depend on your computer's RAM. Loading the final result directly to the Data Model rather than a sheet can further improve performance.
What happens if there are duplicate keys in the second table?
If your shared key column has duplicates in the secondary table, a standard Left Outer Merge will create duplicate rows (a Cartesian product) for every matching key. It is crucial to remove duplicates in the secondary table's key column before performing the merge.
Can I replace the blanks directly in the original column?
Power Query does not allow you to edit cell values directly in place like a traditional spreadsheet. You must always create a new custom or conditional column with the corrected values, and then delete the original column.
