Power Query Problems

How to Update an Excel Master Product File Using Power Query

Elise WilliamsElise Williams Oct 29, 2026 870 views

Question details

The user wants to use Power Query to efficiently update a large master product file using daily data exports.

How to Update an Excel Master Product File Using Power Query
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 you start

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.

Solution 1Recommended

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.

1
Load the Data into Power Query

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.

2
Format the Unique Key

Ensure the 'Product ID' column in both queries is set to the exact same data type (e.g., Text) to prevent matching errors.

3
Merge the Queries

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.

4
Select the Join Kind

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.

5
Expand and Clean the Data

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.

6
Load the Refreshed Data to Excel

Once the data is cleaned, click 'Close & Load' on the Home tab to output the refreshed master list back into your Excel workbook.

Merge Master and Daily Export Files Using Power Query
Testing Best Practice: Always test your Power Query steps on an anonymized copy of your workbook (for example, renaming test sheets to 'Master' and 'Daily Export') before relying on the automated daily refresh.
Free Microsoft Office alternative

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. 1. Download WPS Office: Visit the official WPS website and download the free installation package.
  2. 2. Install the Software: Run the installer and follow the simple on-screen instructions to set up WPS Office on your computer.
  3. 3. Open Your Excel Files: Launch WPS Spreadsheet and open your existing .xlsx master files seamlessly to continue managing your data.
Completely free and lightweight alternative to Microsoft Office.Fully compatible with Microsoft Excel .xlsx, .xls, and .csv file formats.Familiar user interface ensuring a seamless migration with zero learning curve.Built-in advanced data sorting, filtering, and Pivot Table capabilities for robust data management.
microsoft office alternative - wps office

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.