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 six-step analysis in ExploreMyData that flags every policy violation in one pass.

The data

A typical expense report export looks something like this: 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

Open the toolbar and select Convert Type from the Transform group. Pick the amount column and set the target type to DOUBLE.

ExploreMyData uses TRY_CAST(), so if a cell has "$247.50" and the cast fails, it returns NULL rather than blowing up the pipeline. If your amounts have dollar signs or commas, use Find & Replace first to strip them out: replace "$" with "" and "," with "". Then cast.

Step 2: Extract the day of the week

Select Extract Date from the Transform 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

Select Add Column from the Columns group. Name the new column weekend_flag and enter the expression:

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), you can 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.

Step 5: Filter to flagged rows only

Now use Filter to narrow the view. Set the condition to show rows where 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: Catch duplicate submissions

Finally, add a Remove Duplicates step. Select the columns that define a unique expense: employee_name, expense_date, amount, and category.

If the row count drops after this step, you had duplicates. The pipeline sidebar shows the count before and after, so you'll know immediately. Same employee, same date, same amount, same category? That's almost certainly a double submission.

The full pipeline

Six steps, zero code. The pipeline sidebar shows every operation in order:

1. Convert Type (amount to DOUBLE)
2. Extract Date (day of week from expense_date)
3. Add Column (weekend_flag)
4. Add Column (over_limit_flag)
5. Filter (show only flagged rows)
6. Remove Duplicates (employee + date + amount + category)

Each step generates real SQL. You can click any step to see exactly what it does. If the policy limits change next quarter, just edit step 4. If you want to add a new flag for missing receipts, insert another Add Column step after step 4. The pipeline is modular.

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 →