To categorise with a lookup table in Power Query, keep your keywords in a Rules table with a Priority column, add a custom column that filters the rules to those found inside each narration, sort those hits by priority and take the first one. That settles the awkward case where "SWIGGY INSTAMART" matches both a food keyword and a groceries keyword, and it recalculates every time you refresh.
What should the rules table look like?
Create an Excel Table named Rules with three columns. Keyword is the text to look for, Category is what to assign, and Priority is a number where a smaller number wins. Leave no blank keywords, because an empty string is "contained" in every narration.
| Keyword | Category | Priority |
|---|---|---|
| INSTAMART | Groceries | 1 |
| SWIGGY | Eating out | 2 |
| ELECTRICITY | Utilities | 1 |
| UPI | Unsorted UPI | 9 |
Specific words get small numbers and catch-all words get big ones. With the example above, a narration holding both INSTAMART and SWIGGY ends up as Groceries, and a generic UPI line only becomes Unsorted UPI when nothing more specific matched.
How do you load both tables?
- Select any cell in the Rules table and choose Data, then From Table/Range. In the editor, rename the query Rules and set Priority to a whole number type. Close and Load To, choosing Only Create Connection.
- Do the same for your transactions table and name that query Txns.
- Open Txns in the editor, ready for the next step.
What custom column picks the winning rule?
On the Add Column tab choose Custom Column, call it Category, and paste this:
let n = Text.Upper(if [Narration] = null then "" else [Narration]), hits = Table.SelectRows(Rules, each [Keyword] <> null and Text.Contains(n, Text.Upper([Keyword]))), best = Table.FirstN(Table.Sort(hits, {{"Priority", Order.Ascending}, {"Keyword", Order.Descending}}), 1) in if Table.IsEmpty(best) then "Uncategorised" else best{0}[Category]
Reading it from the left: n is the narration in capitals, hits is the Rules rows whose keyword sits inside it, and best is the first hit after sorting by Priority, with the alphabetically later keyword breaking exact ties. The last line returns the category, or "Uncategorised" when nothing matched. The narration is saved in n before the inner each, because inside that each the square brackets refer to the Rules row, not your transaction.
Why not merge the tables instead?
A normal Merge needs an exact match on a column, and a narration is never exactly a keyword. You can force a cross join by adding a constant column to both tables, merging on it, expanding, then keeping rows where the keyword is contained. It works, but it multiplies your row count by the number of rules before filtering, which gets slow on a year of transactions. The custom column above does the same job without that blow-up.
What happens when the rules change?
Edit the Rules table in the workbook, then choose Data, Refresh All. Both queries re-run, so every transaction is re-categorised against the new list. Nothing is stored by hand, which is why changing a priority from 2 to 1 flips old rows too. That is usually what you want, but note it if you have already reviewed last year's categories.
Is a longer keyword always more specific?
Often, but not always, so give priority numbers deliberately instead of relying on length. If you do want length to decide, add a column to Rules with =LEN([@Keyword]) and sort by that descending in place of Priority. Either way, state your rule once and keep it the same.
Next steps
- Add a Rules row each time you see something in Uncategorised.
- Keep Priority numbers sparse (1, 5, 10) so you can slot new rules in between.
- Filter Txns for Uncategorised after every refresh and fix the biggest amounts first.
If maintaining rules by hand gets tiring, SpendixAI's categorisation works in stages, starting with your own rules and then your history and similar merchants, with an AI step only if you leave it on.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

