Build a refreshable expense-category mapping table

A refreshable category map separates classification rules from transaction rows. Instead of editing every new export or extending a long nested IF formula, maintain an approved mapping table and apply it consistently during refresh.

Preserve original labels, make unmatched values visible and require each mapping key to identify one category under the chosen rule. A mapping table with duplicate keys can multiply transactions when merged, while a first-match lookup can conceal the ambiguity.

Define a small classification contract

The spreadsheet quality lab includes a CategoryMap table mapping normalized software, training and support labels to display categories. Its invoice dataset is synthetic; the same mapping mechanics can be applied to expense exports after defining an expense-specific vocabulary.

For an expense example, suppose raw labels are “ Cloud Hosting ”, “cloud hosting”, “Travel” and “Client Meal”. An approved map might assign cloud hosting to Infrastructure, travel to Travel and client meal to Meals. These are operational reporting categories, not tax-treatment advice.

Normalize labels according to a documented rule, such as trimming ordinary surrounding spaces and lowercasing. Keep CategoryRaw alongside NormalizedLabel and Category. Normalization must not discard distinctions the business needs.

Store rules in a visible table

Use columns for NormalizedLabel, Category, RuleOwner and RuleVersion. If rules can change over time, add effective dates or maintain versioned snapshots rather than overwriting history without explanation.

Validate that each normalized label appears once in the active map. If different vendors use the same label with different meanings, expand the key to include the relevant vendor or transaction context. Do not force a many-context problem into one global string lookup.

A category name should have a written definition and examples. “Other” should represent an approved residual class, while “Unmapped” should indicate missing classification evidence. Combining them hides the work still required.

Merge without losing transactions

In Power Query, use the transaction table as the left side and the validated map as the right side. Merge on the normalized key using a left outer join, then expand Category. Preserve rows with no mapping and label their classification status separately.

Microsoft's merge-query documentation describes join selection and expansion. The business controls here establish whether that merge is safe for a reporting table.

For the lab's clean Sales table, seven rows and 72,500 paise must remain seven rows and 72,500 after category enrichment. L07 remains Unmapped with 7,000 paise. An inner join would drop it; a duplicated mapping key could increase rows and amounts.

Review unmatched labels as a queue

Produce an unmatched-label report with raw label, normalized label, transaction count and affected value. Prioritize review by business impact rather than silently assigning the nearest-looking category.

When a reviewer approves a mapping, add it to the controlled map and refresh. Record the decision and version. Do not manually patch the output cells, because a refresh will overwrite those edits and the rule will not apply to future exports.

If fuzzy matching or an AI suggestion is used to propose categories, treat its output as a review candidate. Ambiguous expense labels can require context that a text similarity score does not contain.

Decide whether reclassification changes old reports

A map update can move historical amounts between categories even though transactions are unchanged. Decide whether the organization wants restated history under the latest taxonomy or preserved reporting under the rule version used at the time.

For restatement, retain old and new category totals and explain the bridge. For frozen reports, retain the map version or assigned category snapshot. The correct policy depends on the reporting purpose; neither should happen accidentally.

Test a bad mapping deliberately

Add a second software row with a different category to a copy of CategoryMap. The uniqueness check should fail before the merge is accepted. Remove it, add a valid mapping for the fixture's unknown label and verify that grand totals remain unchanged while the category distribution changes.

The supplied lab checks reference data and workbook structure in Python. Power Query refresh and formula behaviour still need review in the Excel edition used for publication.

Exercise: split one category into two using a new rule version. Document whether January's published report will be restated and show the amount moved between categories without changing the overall total.

NeuraPath's Data Analytics with Generative AI course connects spreadsheet automation with reporting governance. A useful mapping workflow makes classification decisions reusable, inspectable and reversible.

Continue learning

This article is part of the Excel and spreadsheet quality sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Excel percentage points versus percentage change.
• Continue with Excel scenario analysis with explicit assumptions.