Forum Discussion
Blank Cells Item
Hi,
Can someone share their best practice for this type of scenario. I'm new in data cleaning, any help is appreciated. What I wanted to do is to fill the blanks in "Item" column using the "Category and Price" column. The second picture show the whole item under each category. Another one is, there's item in same category that has the same price.
IMHO, some additional logic is to be defined. For example, in first table you have records with no values for item and price. In second table there are few records for such category. Not clear which to take - first one, or one with highest price or what.
Same if we have only category and price, but in second table there are few records for that category with the same price. Which one do you select if do that manually?
3 Replies
IMHO, some additional logic is to be defined. For example, in first table you have records with no values for item and price. In second table there are few records for such category. Not clear which to take - first one, or one with highest price or what.
Same if we have only category and price, but in second table there are few records for that category with the same price. Which one do you select if do that manually?
Thank you! I was able to clean it before checking this thread and this is exactly what I did. Appreciate it.
How about this:
1: Load Both Tables into Power Query
- Select your main table → Data > From Table/Range → name it MainData.
- Select your reference table → Data > From Table/Range → name it ReferenceData.
2: Merge Queries
- In Power Query, go to MainData.
- Click Home > Merge Queries > Merge Queries as New.
- Select ReferenceData as the second table.
- Match on both Category and Price.
- Use a Left Outer Join (keep all rows from MainData).
3: Expand the Merged Table
- Click the expand icon next to the merged column.
- Select only the Item column from ReferenceData.
- Rename it to Item_Lookup.
4: Fill in the Blanks
- Add a Custom Column:
if [Item] = null then [Item_Lookup] else [Item]- Name it Final_Item.
- Remove the original Item and Item_Lookup columns.
- Rename Final_Item to Item.
5: Load the Cleaned Data
Click Close & Load to return the cleaned table to Excel.
