Spreadsheets

Add a custom column in Power Query to flag unusual transactions

Add a custom column in Power Query that flags large or suspiciously round transactions, with the Conditional Column dialog as a no-code alternative.

Editorial illustration for Add a custom column in Power Query to flag unusual transactions

In Power Query, a custom column is a formula applied to every row. To flag unusual transactions, go to Add Column, Custom Column and write: if [Amount] >= 25000 then "Large" else if [Amount] >= 1000 and Number.Mod([Amount], 500) = 0 then "Round" else null. Rows above your threshold are marked Large, big round figures are marked Round, and everything else stays empty.

What does the expression say, word by word?

  • if ... then ... else is the decision. Every if needs an else, which is why the last branch returns null (an empty value).
  • [Amount] is the column, written in square brackets. Column names must match exactly, including capitals.
  • Number.Mod([Amount], 500) = 0 means "the amount divides by 500 with nothing left over". ₹3,500 passes; ₹3,520 does not.
  • and joins two conditions. M is case-sensitive: write and, if, then and else in lowercase, with capital letters for function names like Number.Mod.

The thresholds are examples. Choose numbers that make sense for you, such as the lowest amount you would call large, and the multiples (500, 1,000) that look suspiciously tidy for your usual spending.

What does it produce on sample rows?

AmountTest that firesFlag
₹26,400Above 25,000Large
₹3,500Between 1,000 and 25,000 and divisible by 500Round
₹3,520Noneempty
₹640None (below 1,000)empty
₹25,000At the threshold, so Large wins firstLarge

Order matters, as in a spreadsheet IF. The Large test comes first, so a round ₹25,000 shows as Large and not Round. Put your strongest signal first.

Is there a no-code way?

Yes. Add Column, Conditional Column opens a dialog where you pick the column (Amount), the operator (is greater than or equal to), the value (25000) and the output (Large), then add more clauses and an Otherwise value. It writes the same if-else chain for you. It cannot do the divisible-by test, because the dialog only offers comparisons such as equals, greater than, begins with and contains, so the Round rule needs a Custom Column. You can start with the dialog and then open the formula bar to edit the code.

Why do I get an error instead of a flag?

  • An error mentioning Text and Number: the Amount column is still text. Change its type to Decimal Number before this step. If the conversion itself fails, see the separate article on the DataFormat error.
  • A null amount: comparing null with a number can return null or fail. Add [Amount] <> null and at the start of the condition.
  • The column name was not found: check spelling and spaces. Renaming a column earlier in the query breaks later steps that use the old name.
  • A comma rather than a decimal point in the data, which stops the number conversion.

What do I do with the flag column?

  1. Filter the loaded table on Flag to see only the marked rows.
  2. Compare each against its receipt, a bill or your memory of that day.
  3. Count flags by month with a pivot table or COUNTIFS to see whether they are rising.
  4. Delete the threshold from the formula and put it in a parameter if you change it often, so you edit one cell, not code.

Round-figure spend is also a useful clue for cash. SpendixAI lets you add cash expenses by hand, singly or several at once, and keeps receipts attached to their transactions, which gives you something to check a flagged row against.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports