Build a trustworthy interval table
MMS tables have documented keys, interval conventions and revisions. Joining one-to-many tables carelessly duplicates observations and money. Verify row counts and reconcile energy before calculating cost or revenue.
The lesson
4 chaptersBrowse the chapters
01Specify the observation before opening a spreadsheet
A row needs a meaning before it needs a formula. Write a data contract: the entity, interval, units, time convention, publication cutoff and intended use. In the linked lab a row is one site's average demand over a five-minute interval ending at the timestamp. It is not a generator dispatch target, an instantaneous SCADA reading or a settlement statement. That distinction controls whether multiplying MW by duration is defensible.
Create a field dictionary alongside the raw files. The lab uses interval_end_aest, site, region, revision and average_mw. Average MW is multiplied by 5/60 hours to obtain MWh. AEST is fixed UTC+10 in this exercise. Do not apply a daylight-saving conversion merely because your spreadsheet or computer is set to a local civil timezone.
For actual NEM work, locate the applicable table definition through AEMO's MMS documentation and record the version used. Keys and field meanings are table-specific. The lab intentionally uses its own small schema: copying its revision rule into every real MMS table would be unsafe.
02Separate duplicate delivery from a revised observation
Two identical rows can be a repeated delivery of one observation; two different revision numbers can describe a correction. Preserve the untouched input and put cleaning in a separate sheet or reproducible script. The lab contains eight demand rows but only six site-interval observations: an exact repeat at 09:20 and a replaced reading at 09:10.
Under the lab's explicit extract policy, first remove exact duplicates, then choose the highest revision per site and interval that was available by 10:00. The corrected 09:10 demand is 120 MW, not 100 MW. Keep an audit table explaining which row was removed and why. If two rows share the same key and revision but disagree, stop and investigate instead of using whichever appears first.
Latest-at-extraction is appropriate for this retrospective exercise. It is not appropriate for recreating what a forecaster knew at 08:55. A backtest also needs publication timestamps or historical vintages. Maintain separate event time and availability time; a perfectly clean final dataset can still leak future knowledge into an earlier decision.
03Prove the join before trusting the money
The clean demand key is site plus interval. The price key in the lab is region plus interval. One demand row should match exactly one price after the intended region and price-series selection. Joining only by time adds a second price at 09:15 because the source includes VIC1 as well as NSW1. The resulting seven rows look plausible but count one demand interval twice.
Before merging, count keys on both sides. After merging, report row count, unmatched observations, multiple matches and total MWh. Expected output is six rows and 60 MWh. A lookup that silently returns the first of two matches is not a successful validation. Missing price is not zero price: a genuine zero exists at 09:25 and must remain distinguishable from absent data.
In Excel, create a combined interval-and-region key and use COUNTIF to verify one price match before XLOOKUP. In SQL, group by the proposed key and inspect HAVING COUNT(*) <> 1 before joining. Retain unmatched rows for investigation using a left join rather than losing them inside an inner join. These are controls around a calculation, not cosmetic data preparation.
04Reconcile interval value and make the result repeatable
Calculate MWh and cost row by row. The six clean lab energies are 5, 10, 15, 10, 5 and 15 MWh. Multiply them by the matching prices to obtain A$250, −A$200, A$1,500, A$2,000, A$0 and A$1,200. Sum energy separately from money: 60 MWh and A$4,750. Negative prices are retained; a negative energy-only cost is not a promise of a negative final customer bill.
Divide total cost by total energy to obtain A$79.17/MWh. The simple price average is A$68.33/MWh because it weights intervals equally rather than by consumption. Do not average MW or prices indiscriminately when durations or volumes differ. Explain the denominator and preserve enough unrounded precision to reproduce the total.
Your work product should contain raw files, the field dictionary, cleaning log, clean joined output, formulas, totals and limitations. Ask a reviewer to regenerate the clean table without verbal help. A chart is useful only after the data reconciles; an attractive chart cannot repair a duplicated join or an unstated time convention.
Watch the explanation
Supporting diagrams
Protect the result at every hand-off
The lab's row counts are control totals, not a measure of progress.
- 01 · Raw demand8 rows
One exact repeat and one superseded revision
- 02 · Clean observations6 rows
One site observation per five-minute interval
- 03 · Matched output6 rows
Exactly one NSW1 price per interval