Use Excel Power Query to Fill Null Categories, Match Brands, and Remove Rows
Question details
Use Power Query to fill missing categories, identify brands, and remove records that do not belong to approved brands.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A CSV file is updated weekly and needs repeatable category completion and brand-based filtering.
- Observed behavior
- The desired result is a refreshable query that fills missing values and retains only records matching an approved-brand list.
Check that the CSV has the expected Category, Subcategory, Product Name, and Brand columns. Create an Excel table of approved brands with a Brand column, and decide how brand names should be spelled in the cleaned data.
Build a refreshable cleanup query with an approved-brand table
Use conditional logic for categories and an inner merge to keep only approved brands when the CSV is refreshed.
An approved-brand list is easier to maintain than a fixed competitor exclusion list: newly appearing brands are excluded until they are approved.
An exact merge matches whole brand values, not brand names embedded in product names. Identify the brand first if it is only present in Product Name.
In Excel, select Data > Get Data > From File > From Text/CSV, choose the CSV, and select Transform Data. Select the approved-brand table in Excel and use Data > From Table/Range to create a separate query.
In the CSV query, select Add Column > Custom Column and enter: if [Subcategory] = "Barbie" and [Category] = null then "Doll" else [Category]. Name the column Category Clean, then remove the original Category column and rename Category Clean to Category.
If Brand is missing but Product Name contains it, select Add Column > Custom Column and add rules for the expected names. For example: if [Brand] <> null then [Brand] else if [Product Name] <> null and Text.Contains([Product Name], "Barbie", Comparer.OrdinalIgnoreCase) then "Barbie" else null. Extend the rules for a small brand list, then replace the original Brand column with the cleaned column.
Select Home > Merge Queries. Choose the approved-brand query, select Brand in both tables, and choose Inner join to retain matching rows only. Remove the resulting nested-table column if you do not need its fields.
Select Home > Close & Load. After updating the CSV at the same file path, select Data > Refresh All to rerun the cleanup and merge.

Work with spreadsheet data in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and compatibility with Excel files for easier migration. Use Excel Power Query for the query-based cleanup described above; WPS Office is an alternative for working with the resulting spreadsheet.

Frequently Asked Questions
How do I replace a null category only for a specific subcategory?
In Power Query Editor, select Add Column > Custom Column and use a condition such as if [Subcategory] = "Barbie" and [Category] = null then "Doll" else [Category]. Remove the original column and rename the new one if you want to keep the Category name.
What happens when a new competitor appears in the weekly CSV?
An inner merge with an approved-brand table excludes any brand that does not match the list. Add a brand to the approved table and refresh the query if it should be retained.
Can I match a brand that appears inside a product name?
For a small list, use Add Column > Custom Column with Text.Contains and Comparer.OrdinalIgnoreCase to identify known names. An ordinary merge on Brand matches the whole value, so it will not automatically extract a brand from Product Name.
Should I use fuzzy matching for the approved-brand merge?
Fuzzy matching can help with variations in brand values. In Home > Merge Queries, enable fuzzy matching and test its settings against your data; review results because similar but unapproved names may match.
How do I apply the cleanup to the next CSV update?
Update the CSV at the same source path, then select Data > Refresh All in Excel. Keep the expected column names consistent so the existing query steps can find them.




