How to Fill Blank Excel Item Cells Using Category and Price in Power Query
Question details
The user needs to populate missing item values in an Excel dataset by matching category and price data against a secondary reference table.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Cleaning and merging data where some item names are missing, but category and price attributes are available to perform a lookup.
- Observed behavior
- Item cells are blank and need to be filled automatically using lookup criteria from another table via Power Query.
Ensure your main dataset and reference dataset are both formatted as Excel Tables (select your data and press Ctrl+T) before loading them into Power Query.
Use Power Query to Merge Tables and Fill Blanks
Load both tables into Power Query, perform a Left Outer Join matching Category and Price, and use a conditional column to fill in the missing items.
Power Query allows you to join multiple tables based on common columns, similar to a database operation. By merging your main table with your reference table based on Category and Price, you can extract the correct item names and fill in the blank cells without writing complex array formulas.
Select your main table, go to the Data tab on the Excel ribbon, and click 'From Table/Range'. Name this query MainData in the Power Query Editor. Close and load it as a connection, then repeat this process for your reference table and name it ReferenceData.
In the Power Query Editor for MainData, navigate to Home > Merge Queries > Merge Queries as New. Select ReferenceData as the second table in the dialog box. Hold the Ctrl key to select both the Category and Price columns in both tables to match them up. Ensure the Join Kind is set to 'Left Outer' to keep all original rows.
You will see a new column containing 'Table' objects. Click the expand icon (two diverging arrows) on the top right of the merged column header. Uncheck all columns except the 'Item' column, and rename it to 'Item_Lookup' to differentiate it from your original item column.
Go to the Add Column tab and click 'Custom Column'. In the formula box, enter exactly: `if [Item] = null then [Item_Lookup] else [Item]`. Name this new column 'Final_Item' and click OK.
Select the original 'Item' and 'Item_Lookup' columns, right-click, and choose 'Remove Columns'. Rename your 'Final_Item' column to 'Item'. Finally, go to the Home tab and click 'Close & Load' to output the completed, cleaned table back into Excel.
Simplify Your Spreadsheet Tasks with WPS Office
While Power Query is a feature specific to Microsoft Excel, you can achieve multi-condition lookups effortlessly using advanced formulas in WPS Spreadsheet. WPS Office is a free, lightweight, and comprehensive suite that provides a seamless alternative for your daily data processing needs.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office installer.
- 2. Install the suite: Run the setup file and follow the on-screen instructions to complete the lightweight installation.
- 3. Open your spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx file to handle complex data lookups seamlessly.
Frequently Asked Questions
Can I perform a multi-criteria lookup without Power Query?
Yes. If you prefer using formulas instead of Power Query, you can use the INDEX and MATCH functions together as an array formula, or the XLOOKUP function, to match both Category and Price and return the Item name.
Why is my Power Query merge not finding any matches?
Matches might fail due to invisible trailing spaces or case sensitivity differences (e.g., 'Apple' vs 'apple'). You can select your columns in Power Query, right-click, and use Transform > Trim, followed by Transform > capitalize Each Word to standardize the text before merging.
What does a 'Left Outer' join mean in this context?
A Left Outer join keeps all the rows from your first (main) table and only pulls in the matching rows from the second (reference) table. This ensures your original dataset remains intact and no records are accidentally deleted if a match isn't found.
