How to Update Thousands of Product Prices Using Excel Power Query
Question details
The user needs to efficiently update a large list of thousands of product prices by comparing an existing database with new supplier CSV or XLS files.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Receiving periodic bulk price updates from suppliers and needing to apply these changes to a master inventory list based on matching SKUs.
- Observed behavior
- Manual price updating is highly inefficient and prone to error. An automated, repeatable process is required to match products by SKU and replace outdated prices with current values.
Ensure both your existing price list and the new supplier file are saved in standard formats (like XLSX or CSV) and that both tables share a unique identifier column, such as a SKU or Product ID.
Merge Price Lists Using Power Query
Import both tables into Power Query and use the Merge Queries feature to automatically match SKUs and update prices.
Power Query can easily handle tens of thousands of rows, making it far more robust and repeatable than traditional VLOOKUP formulas. By linking the old and new tables via the SKU column, you can seamlessly overwrite old prices while preserving existing data for unchanged items.
Open a new blank Excel workbook. Go to the 'Data' tab, click 'Get Data', and select 'From File' to import both your existing master price list and the new supplier list into the Power Query Editor.
In the Power Query Editor, select your master price list query. Navigate to the 'Home' tab and click 'Merge Queries'. In the dialog box, select the new supplier list from the dropdown to be the second table.
Click on the 'SKU' or 'Product ID' column in the top preview window, then click the exact same identifying column in the bottom preview window. Ensure the Join Kind is set to 'Left Outer', then click 'OK'.
A new column will appear at the end of your master table. Click the expand icon (two diverging arrows) on the column header. Uncheck all columns except the 'New Price' column, and click 'OK'.
You can now replace your old price column with the new one by adding a conditional column (if the new price is null, keep the old price). Once finalized, click 'Close & Load' on the Home tab to output the updated list back into Excel.
Manage Massive Product Lists Smoothly with WPS Office
While Power Query is a dedicated Microsoft Excel feature, you can handle large-scale data updates easily using WPS Office. As a fully compatible, lightweight Office alternative, WPS Spreadsheets handles massive datasets effortlessly, allowing you to seamlessly process CSV or XLSX supplier files with advanced lookup tools.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your device in minutes.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheets and open your master inventory Excel file alongside your supplier's CSV file.
- 3. Process the Updates: Utilize robust built-in features like XLOOKUP or VLOOKUP to match SKUs and merge thousands of price updates seamlessly.
Frequently Asked Questions
Can I use Power Query to pull supplier prices directly from a CSV file?
Yes, Power Query natively supports CSV files. Simply navigate to the 'Data' tab, select 'Get Data', then choose 'From File' > 'From Text/CSV' to directly link the supplier's raw text file to your update process.
What happens if a product is in my master list but missing from the new supplier file?
If you perform a 'Left Outer' join during the Merge Queries step, any product missing from the supplier file will return a 'null' value in the new price column. You can easily use a conditional column rule in Power Query to state: 'If New Price equals null, output Old Price'.
How do I reuse this price update process for next month's supplier file?
To reuse the process, simply save your new supplier file with the exact same file name and in the exact same folder location as the previous one (overwriting it). Then, open your master Excel workbook, go to the 'Data' tab, and click 'Refresh All'.
Will Excel freeze or crash when updating 20,000 products?
Using Power Query is significantly more memory-efficient than applying VLOOKUP formulas across 20,000 rows. Power Query processes the matching in the background data model, heavily reducing the likelihood of freezing or crashing compared to traditional spreadsheet formulas.
