How to Update an Excel Master Product File Using Power Query
Question details
The user wants to use Power Query to efficiently update a large master product file using daily data exports.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Updating a master inventory or product catalog with daily exported data.
- Observed behavior
- The user needs a method to identify updated, new, and missing products by matching unique product IDs across datasets.
Before setting up your query, ensure both your Master Product File and Daily Export file have matching column headers and a consistent unique identifier (like a Product ID) formatted as the same data type.
Merge Master and Daily Export Files Using Power Query
The most efficient way to update a master list is by loading both tables into Power Query and performing a merge operation based on the unique Product ID.
This approach allows you to join two datasets dynamically. By using a Full Outer Join, you can easily identify matching records to update, new records to add, and missing records to review.
Go to the 'Data' tab, click 'Get Data' > 'From File' > 'From Workbook', and load both your Master file and the Daily Export file into the Power Query Editor.
Ensure the 'Product ID' column in both queries is set to the exact same data type (e.g., Text) to prevent matching errors.
Select your Master query, go to the 'Home' tab, and click 'Merge Queries' > 'Merge Queries as New'. Choose the Master table as the first table and the Daily Export as the second. Click the 'Product ID' column in both preview windows to link them.
In the Join Kind dropdown, choose 'Full Outer (all rows from both)'. This ensures no data is dropped, allowing you to catch new, updated, and missing products.
Click the expand icon on the newly merged column to reveal the daily data. Use Conditional Columns ('Add Column' > 'Conditional Column') to overwrite old prices or stock levels with the daily export data if it is not null.
Once the data is cleaned, click 'Close & Load' on the Home tab to output the refreshed master list back into your Excel workbook.

Handle Large Datasets Efficiently with WPS Office
While Power Query is a specific feature of Microsoft Excel, WPS Office provides a highly compatible, lightweight, and free alternative for everyday data management, pivot tables, and VLOOKUP tasks without the heavy system resource usage.
- 1. Download WPS Office: Visit the official WPS website and download the free installation package.
- 2. Install the Software: Run the installer and follow the simple on-screen instructions to set up WPS Office on your computer.
- 3. Open Your Excel Files: Launch WPS Spreadsheet and open your existing .xlsx master files seamlessly to continue managing your data.

Frequently Asked Questions
What is the best Join Kind for updating a master list in Power Query?
A 'Full Outer Join' is typically the best choice because it retains all records from both the master and daily export files. This allows you to easily identify which products are brand new, which have been updated, and which are missing from the recent export.
How do I handle products that are in the master file but missing from the daily export?
After performing a Full Outer Join, any products missing from the daily export will display 'Null' values in the expanded daily export columns. You can use a Conditional Column to flag these specific items as 'Discontinued', 'Out of Stock', or simply retain their old values.
Why is Power Query not matching my Product IDs correctly?
This almost always happens when the data types do not match between the two tables. Ensure the Product ID column is formatted exactly the same (e.g., both explicitly set to 'Text') in both the Master and Daily Export queries before attempting the merge.
Can I automate this daily refresh process?
Yes. Once your query is built and loaded to a worksheet, you can simply overwrite the old daily export file in its folder with the new one, open your master workbook, and click 'Data' > 'Refresh All' to update the master list instantly.




