Analyzing Expense Reports for Policy Violations

Someone on the finance team dumps the quarterly expense data into a spreadsheet and asks: "Can you check these for anything weird?" There are 3,200 rows. You need to find amounts over the $500 threshold that didn't get manager approval, charges that happened on weekends, meals that exceed the per-diem, and duplicate submissions. Doing this manually means hours of scrolling and conditional formatting. Or you build a pipeline.

Here's how to set up a seven-step analysis in ExploreMyData: five cards that flag every policy violation in one pass, and two more that put the duplicate submissions on screen.

The data

A typical expense report export looks something like this: an expense id, employee name, department, expense date, category (meals, travel, supplies), amount, receipt attached (yes/no), and manager approval status. The amount column is almost always imported as text because someone left a dollar sign in there. The dates might be strings. The first two steps fix that.

Step 1: Convert the amount to a number

Click the green + in the Pipeline panel and select Convert Type from the Transform group. Pick the amount column and set "Convert to" to numeric. That is the whole step.

You do not need to scrub the dollar signs first. Text to numeric strips $, ,, €, £, ₹, % and stray spaces on its own, and it reads accounting-style parentheses as negatives, so (500) lands as -500. A raw $1,234.56 comes out as 1234.56 in one step. Skip the Find & Replace; it is one more card to maintain for no gain.

The cast itself goes through TRY_CAST(), so anything that genuinely isn't a number (a stray "N/A", a note someone typed into the amount cell) becomes NULL rather than killing the step. Sort by amount afterwards and glance at the NULLs. Those rows are usually worth a look on their own.

Step 2: Extract the day of the week

Click the green + in the Pipeline panel and select Extract Date Part from the Date group. Choose the expense_date column and extract the "day of week" part. This creates a new column with values 0 (Sunday) through 6 (Saturday). Under the hood, it runs EXTRACT(DOW FROM "expense_date").

Now you have a numeric day-of-week column to work with. Sunday = 0, Saturday = 6. The numbers matter for the next step.

Step 3: Flag weekend charges

Click the green + in the Pipeline panel and select Add Column from the Columns group. Name the new column weekend_flag and put this in the Expression box:

CASE WHEN "expense_date_dow" IN (0, 6) THEN 'weekend' ELSE NULL END

Any expense filed on a Saturday or Sunday now gets a clear "weekend" tag. NULL means it's a normal weekday charge. Why flag weekends? Most company policies require additional justification for weekend expenses, and some categories (like meals) aren't covered at all outside business days.

Step 4: Flag amounts over the policy limit

Add another column. Call it over_limit_flag. The expression depends on your policy. If the rule is "anything over $500 without approval gets flagged":

CASE WHEN "amount" > 500 AND "approval_status" != 'approved' THEN 'over_limit' ELSE NULL END

If you have per-category limits (meals over $75, travel over $500, supplies over $200), build a more specific expression:

CASE WHEN ("category" = 'meals' AND "amount" > 75) OR ("category" = 'travel' AND "amount" > 500) OR ("category" = 'supplies' AND "amount" > 200) THEN 'over_limit' ELSE NULL END

Either way, you end up with a column that's NULL for compliant expenses and "over_limit" for everything that needs a second look. The table below uses the per-category version, which is why an approved $88.50 meal still gets flagged.

Step 5: Filter to flagged rows only

Now click the green + in the Pipeline panel and select Filter from the Filter & Sort group. Set the condition to "weekend_flag" IS NOT NULL OR "over_limit_flag" IS NOT NULL. This gives you every row that tripped at least one policy check.

From 3,200 rows, you might be down to 47. That's the list your finance team actually needs to review. Each row still has all the original columns plus the two flag columns, so you can see exactly what triggered the flag.

Step 6: Surface duplicate submissions

Duplicates are the last thing on the finance team's list, and they need different handling from the flags. You are not trying to clean them away. You want the offending pairs on screen with names attached, so somebody can go and ask what happened.

Reach for Remove Duplicates here and you get the opposite of what you need: it silently deletes the second copy and hands you a shorter table with no record of what went missing. Useful when you're cleaning a mailing list, useless when the duplicate is the finding.

Count them instead. Click the green + in the Pipeline panel and select Group & Aggregate from the Aggregate group. Set "Group by" to the four columns that define a unique expense: employee_name, expense_date, amount, category. Then add one aggregation: function COUNT, column expense_id.

There is no output-name field on that panel. The alias is always the column name and the function stuck together, so you get expense_id_count. Worth knowing before you go looking for a name you invented. Note also that the four grouped columns drop out of the aggregation column list, which is why the count runs on expense_id rather than on one of the keys.

Now add a Filter with the condition "expense_id_count" > 1. What is left is a list of the exact expense claims that were submitted more than once, with the count next to each. Same employee, same date, same amount, same category, filed twice. That is almost never a coincidence, and now you can name it in an email.

One thing to plan for: Group & Aggregate collapses the table to one row per key, so the flag columns from steps 3 and 4 do not survive it. Treat this as a second question rather than a continuation. Export the flagged list from step 5 first, then delete the step 5 Filter card and add these two on top. Cards can be deleted but not reordered, so build them in the order you want them to run.

The full pipeline

Seven cards and not a formula in sight. The pipeline sidebar shows every operation in order:

1. Convert Type (amount to numeric)
2. Extract Date Part (day of week from expense_date)
3. Add Column (weekend_flag)
4. Add Column (over_limit_flag)
5. Filter (show only flagged rows)
6. Group & Aggregate (COUNT of expense_id by employee + date + amount + category)
7. Filter (expense_id_count > 1)

Each step generates real SQL, and each step is its own view. Click any card to see exactly what it does. If the policy limits change next quarter, edit the card for step 4 and everything downstream recomputes. If you want a new flag for missing receipts, add another Add Column card.

The next time finance sends you 3,200 rows, you load the file and the pipeline runs in under a second. No scrolling, no conditional formatting, no formulas that break when someone adds a row.

Build your expense analysis pipeline →