Power Query Problems

Use Excel Power Query to Fill Null Categories, Match Brands, and Remove Rows

Camila MilosovichCamila Milosovich Nov 18, 2026 869 views

Question details

Use Power Query to fill missing categories, identify brands, and remove records that do not belong to approved brands.

Use Excel Power Query to Fill Null Categories, Match Brands, and Remove Rows
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.
Before you start

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.

Solution 1Recommended

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.

1
Import the CSV and brand list

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.

2
Fill missing categories

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.

3
Identify brands when needed

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.

4
Merge with approved brands

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.

5
Load and refresh

Select Home > Close & Load. After updating the CSV at the same file path, select Data > Refresh All to rerun the cleanup and merge.

Build a refreshable cleanup query with an approved-brand table
Check matching rules: Exact matches can be affected by capitalization and extra spaces. Standardize brand values in both queries or use the merge dialog's fuzzy matching options after testing the results. Substring rules can also match unintended product names, so review ambiguous cases.
Free Microsoft Office alternative

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.

Open and edit Excel-compatible XLSX and XLS filesReview cleaned brand and category data in a spreadsheetUse a familiar interface for everyday spreadsheet workMove existing Office files into a lightweight workspace
microsoft office alternative - wps office

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.