Learning goals

Add a custom column with a date calculation: the NET 30 due date
Add a conditional column with a UI-driven if/then/else chain: the aging bucket
Know when to compute inside the query and when to keep a worksheet formula
Rebuild the Paid / Overdue / Open status formula and verify it against the bucket column

Concepts

Custom Column: a formula over the row

Add Column > Custom Column opens a dialog with one formula box and the column list on the right. Double-click a column to drop it into the formula wrapped in brackets -- [Invoice Date], not C5. There are no cell references in Power Query: every expression is evaluated for the whole row at once.

Northgate's first custom column is the NET 30 due date: Date.AddDays([Invoice Date], 30). Invoice terms are NET 30 -- payment is due thirty days after the invoice date -- so INV-2401, dated Jul 8, 2026, is due Aug 7, 2026, and INV-2404, dated Jul 30, 2026, is due Aug 29, 2026. In the teaching workbook the same column appears as the pale-green worksheet formula =$C5+30 so the browser can recalculate it; in your live workbook the query computes it once and it never drifts.

=$C5+30

The worksheet mirror of the custom column: invoice date plus thirty days. Date arithmetic in Excel is days, so no DATE function is needed. $C5 is anchored on the column so the formula can be filled down every row without the reference sliding sideways.

Conditional Column: if/then/else by clicking

Add Column > Conditional Column gives you a form: column name, operator, value, output -- and an else. No formula syntax to memorize. Northgate's aging bucket reads: if Paid Date is not null then Closed; else if the invoice has been open thirty days or less at the cutoff then 0-30; else 31-60. At the Aug 31, 2026 cutoff that puts INV-2404 (open thirty-two days) in 31-60 and every other unpaid August invoice in 0-30.

The cutoff itself is a query decision: Date.EndOfMonth of the report month, not TODAY(). A report that ages against the clock gives different answers depending on when you open it, which is fine for a dashboard and wrong for a close. Every number in a monthly report should be reproducible.

=IF($L5<=0,"Paid",IF($D5<Settings!$C$7,"Overdue","Open"))

The status column, and the worksheet twin of the same logic: if the open balance (L5) is zero or less the invoice is Paid; otherwise, if the due date (D5) falls before the close date in Settings!$C$7 it is Overdue; otherwise Open. Two nested IFs, three outcomes, one setting -- change the close date and the classification follows.

Query or worksheet formula?

You now have two places to compute. The rule Northgate follows: values that describe a ROW belong in the query (due date, aging bucket, service line) so they travel with the data wherever it loads; values that describe the REPORT belong on the sheet (period totals, counts, utilization) so they stay visible and auditable next to the report itself.

The tell that you chose wrong is fragility. A worksheet formula sitting beside a loaded table breaks when the table grows past it or arrives sorted differently; a query column cannot break, it can only be wrong -- and being wrong shows up in the check figures. Keep the row-level truth in the query and let the report layer read it.

💡 Tips:

Custom Column accepts nested logic too (each [X] and [Y] ... else ...), but past two branches the Conditional Column form is easier to audit -- and easier for the next person to audit, which is the real test.

null is not 0 in Power Query. A blank Paid Date must be tested with 'is null'; comparing it to a number returns null, never TRUE or FALSE.

Practice

The exercise opens on Invoice Facts, the sheet where all three computed columns live together. Cell M5 is blank -- you retype the status formula. Nothing is saved in the browser.

Read the due dates — Column D is the NET 30 column: INV-2401 due Aug 7, 2026; INV-2404 due Aug 29, 2026; INV-2405 due Sep 3, 2026; INV-2413 due Oct 2, 2026. Each one is its row's invoice date plus thirty days.
Retype the status formula — Cell M5 is empty. Type =IF($L5<=0,"Paid",IF($D5<Settings!$C$7,"Overdue","Open")) and press Enter: Paid, because INV-2401's open balance is zero. Only row 8 (INV-2404, due Aug 29 against the Aug 31 close) returns Overdue.
Cross-check the two logics — Compare column M (status) with column N (aging bucket) row by row. Paid always pairs with Closed; the single Overdue pairs with 31-60; every Open pairs with 0-30. Two independent calculations agreeing is the cheapest audit there is.
Watch the boundary rows — INV-2408 is Paid even though its payment landed Sep 3, 2026 -- after the close. Status describes the ledger; period totals in lesson 10 describe the window, and the two are allowed to disagree. INV-2413, dated Sep 2, 2026, is Open with bucket 0-30 and will be invisible to every August metric.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

☐ Retyped the status formula into M5 and got Paid for INV-2401
☐ Confirmed every Paid row pairs with a Closed aging bucket and the one Overdue row (INV-2404) pairs with 31-60
☐ Verified the baseline: 930.00 overdue at the Aug 31, 2026 close, from INV-2404's NET 30 due date of Aug 29, 2026

⚡ Lesson 07 · Custom and conditional columns