How to Update an Excel Table with New and Missing Data Using Power Query
Question details
The user needs to compare an existing Excel table with external data to update quantities, insert new items, and remove obsolete ones while preserving descriptive details.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Updating an inventory or master list with a new external data source without losing existing custom descriptions.
- Observed behavior
- The user wants to synchronize an existing table with an external dataset, ensuring new items are added, matching items are updated, missing items are removed, and specific columns are retained.
Ensure both your existing table and the external data source are formatted as Excel Tables (Ctrl + T) and contain a unique identifier column, such as an Item Number, to serve as the matching key.
Use Power Query to Merge External and Existing Tables
Import both datasets into Power Query and use a Left Outer Join merge operation to synchronize the lists, keeping the external data as the primary source of truth.
Power Query allows you to dynamically link an external data source to your existing table. By performing a merge, you can pull in updated quantities and new rows while keeping the custom descriptive columns you already created.
Select your existing table, go to the 'Data' tab, and click 'From Table/Range'. In the Power Query Editor, click 'Close & Load To...' and choose 'Only Create Connection'. Repeat this step for your external data table.
In Excel, go to the 'Data' tab, click 'Get Data' > 'Combine Queries' > 'Merge'. Alternatively, open the Power Query Editor, select the external data query, and go to 'Home' > 'Merge Queries' > 'Merge Queries as New'.
In the Merge dialog box, select the external data table from the first dropdown and the existing table from the second dropdown. Click on the unique identifier column (e.g., Item Number) in both tables to link them. Choose 'Left Outer (all from first, matching from second)' as the Join Kind and click OK.
In the newly merged query, locate the column that contains the 'Table' values at the far right. Click the expand icon (two diverging arrows) in the column header. Select the specific descriptive columns you want to retain from your existing table and uncheck 'Use original column name as prefix'.
Review the data to ensure new items appear (their expanded existing columns will show as 'null') and quantities are updated. Once satisfied, click 'Close & Load' on the Home tab to output the fully synchronized table into a new worksheet.
Try WPS Office for Seamless Data Management
While complex Power Query setups are specific to Microsoft Excel, WPS Office provides a free, lightweight, and highly compatible alternative for everyday spreadsheet management, data comparison, and merging tasks.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your PC or Mac.
- 2. Open Your Excel File: Launch WPS Spreadsheets and open your existing .xlsx file with full formatting intact.
- 3. Analyze and Merge Data: Utilize built-in data comparison features and powerful lookup formulas to manage your tables easily.
Frequently Asked Questions
How do I identify which items are brand new when merging tables?
When you perform a Left Outer Join with the external data as the primary table, look at the expanded columns from your old table. Any row where the old descriptive data shows as 'null' indicates a new item that was not present in your original list.
Will using Power Query delete my original existing table?
No. Power Query never alters the original source data. It extracts the information, applies your merge steps in memory, and loads the combined result into an entirely new worksheet, keeping your original tables safe.
What happens to items that are no longer in the external data source?
Because you are using the external data as the primary table in a Left Outer Join, the query only looks at the external list. Any items that exist in your old table but not in the new external data will be naturally excluded from the final merged output.
