ontario-bps-energy-dashboard
Modeling energy use and GHG emissions across Ontario's Broader Public Sector in Power BI
Why this project
This project pairs with the Ontario Energy Mix analysis as the BI-building half of a two-part portfolio: where that project went deep on SQL and Python analytical rigour, this one is a companion piece for the PL-300 (Power BI Data Analyst Associate) certification — proving out data modeling, Power Query, DAX, and report design on a dataset that's actually messy, not a tutorial-clean sample.
Ontario's Broader Public Sector Energy Use and GHG Emissions dataset — every municipality, school board, university, college, and hospital reporting under O. Reg. 25/23 — was the right fit precisely because it isn't clean: real column drift between years, and poor data quality from everything being self-reported. The goal wasn't to chase a novel research finding — it was to take a real, messy, regulator-published dataset and build the kind of end-to-end BI product a working analyst actually ships: a cleaned star schema, a DAX layer that holds up under dynamic filtering, and a report that a non-technical stakeholder could open and immediately understand.
How it was built
Three years of Ontario's regulator-published Excel exports (2021–2023 — the one stretch where the schema stays consistent enough to combine cleanly) were reconciled in Power Query: renamed and added columns so 2021 matched 2022–2023 exactly, roughly 15 unreliable or redundant fields dropped (a Number of Buildings count that reports "1" for both a single office and a 50-building campus, six near-empty legacy energy columns since consolidated into one, admin metadata with no analytical value), and "Not Available" handled differently depending on what it actually meant — a true zero for a single fuel type a property doesn't use, but a genuine null at the Site Energy Use/GHG level, where it means the property didn't report at all.
The cleaned data was modeled into a proper star schema — fact_energy, dim_building, dim_year — with dim_building built by grouping to each property's most recently reported attributes rather than trusting row order, plus a postal-code validation chain and a Toronto-borough fold (Scarborough, North York, etc. folded into "Toronto") to make the City field usable for mapping. A GFA outlier-correction rule — nulling implausible floor-area values that matched one of three specific patterns found in the data, checked afterward to confirm no row exceeds a sane energy-intensity ceiling — keeps every efficiency comparison honest.
On top of that: DAX measures built around CALCULATE/FILTER/ALL for property-type-relative targets and share-of-total metrics, Field Parameters driving a shared metric slicer across every report page, and bookmarks handling both hidden filter panels and a structural Power BI limitation — drillthrough filters can't be cleared by a bookmark, solved by splitting one planned page into two: a true drillthrough page, and a twin "Explore" page with ordinary, bookmark-resettable slicers.
Key highlights
- ~18,000 Property IDs (~17,000 real buildings) modeled across three years on a proper star schema, built entirely from Ontario's raw regulator Excel exports
- A genuine Power BI platform limitation — bookmarks can't clear a drillthrough filter — solved architecturally by splitting one intended page into two, rather than fighting the platform
- A real data-quality anomaly, measured instead of guessed at: 1,751 of 17,011 (Organization, Property Name) pairs — nearly all City of Toronto — show a pattern consistent with a one-time Portfolio Manager ID reissue; quantified precisely, but deliberately left unmerged, since a name pair isn't a guaranteed unique building identifier
- Field Parameters drive every report page's metric slicer, which uncovered a Power BI Service only color bug that reverted line and marker colors to default blue on publish. It took three attempts to fix
Below is the live Exec Dashboard page, embedded directly — the other pages are one click away via the sidebar.
Code highlight
How a property's GHG target is calculated — the median emissions intensity for its property type, scaled to its own floor area (DAX):
Target Emissions (t CO2e) =
VAR CurrentUseType = SELECTEDVALUE(
dim_building[Primary Property Type - Self Selected], BLANK()
)
VAR MedianEmissionIntensity = CALCULATE(
MEDIANX(
fact_energy,
DIVIDE(
fact_energy[Total (Location-Based) GHG Emissions (Metric Tons CO2e)],
fact_energy[Adjusted GFA],
BLANK()
)
),
FILTER(
ALL(dim_building),
dim_building[Primary Property Type - Self Selected] = CurrentUseType
)
)
RETURN SUM(fact_energy[Adjusted GFA]) * MedianEmissionIntensity