You opened Power BI to build an energy dashboard, and three weeks later you are still in spreadsheets. That is the normal experience. The visuals are the easy part. The work that eats the calendar is collecting complete consumption and billing data for every account, in every commodity, from every utility, and forcing it into a shape Power BI can actually model. This guide shows what a good utility data model looks like, and how clean, standardized data delivered by API, CSV, or SFTP removes the gathering step so you can spend your time on measures instead of cleanup.
The bottleneck is the data, not the dashboard
Most portfolios pull data from a mix of utility portals, PDF bills, and the occasional spreadsheet a property manager emails over. The formats never agree. One utility reports kilowatt hours, another reports the same thing in a slightly different column layout. Gas shows up in cubic metres on one bill and gigajoules on the next. Billing periods run 28 to 34 days and rarely line up across accounts. Meter numbers change when a meter is swapped. Before you can write a single measure, you have to reconcile all of that by hand, and you have to redo it every month.
None of that reconciliation belongs inside Power BI. Power Query can bend a lot of shapes, but it is not the right place to fix upstream data problems that recur every billing cycle. The cleaner move is to standardize consumption and billing data once, at the source, and hand Power BI a tidy set of tables it can just load.
If you want to skip the reconciliation entirely, start a pilot from the plans at ezgb.ca and we will connect your accounts and hand you standardized electricity, gas, and water data in the delivery format your Power BI model expects.
What a good utility data model looks like
Microsoft recommends organizing a Power BI model as a star schema, where every table is classified as either a fact table (the numbers you summarize) or a dimension table (the attributes you filter and group by). The same guidance stresses that a fact table should always load data at a consistent grain. Utility data fits this pattern cleanly once it is standardized.
The fact tables
Energy portfolios usually need two fact grains, and it is worth keeping them in separate tables so each stays at a consistent grain:
- Meter reads / interval usage: one row per meter per interval (monthly, daily, or the finer intervals available from interval data), holding consumption and, where relevant, demand.
- Bills / charges: one row per account per billing period, holding total cost, consumption billed, demand billed, taxes, and delivery versus commodity charge splits.
The dimension tables
Around those facts sit the tables you slice by. A dedicated date table is the one non negotiable, because time intelligence measures depend on it:
- Date: a continuous calendar marked as the model date table, so month, quarter, and year comparisons work.
- Account / meter: account number, meter number, commodity, and the service address.
- Site / property: building name, gross floor area, city, and portfolio grouping, which is what lets you normalize by area later.
- Utility and commodity: the distributor and the fuel (electricity, natural gas, water), so multi commodity roll ups stay clean.
How EZGB delivers data ready to model
EZGB (Easy Green Button Connector) collects utility consumption and billing data from three places: authorized utility connections, Green Button, and PDF or scanned bill digitization through BillScan. Green Button itself is built on the North American Energy Standards Board (NAESB) Energy Services Provider Interface (ESPI) standard, and its Download My Data and Connect My Data methods are certified by the Green Button Alliance. In Ontario, the province's energy data regulation now requires utilities to offer Green Button, which is why more than 50 Ontario utilities have implemented it.
Whatever the source, EZGB normalizes the output so your model does not have to. You get consistent units, consistent commodity labels, stable account and meter identifiers, and clearly separated consumption and billing fields, delivered by REST API, CSV, or SFTP. That maps directly onto the fact and dimension tables above.
Refreshing the model: API, SFTP, and scheduled refresh
The delivery method you choose shapes how the model refreshes:
- REST API: connect Power BI to the endpoint with the Web connector in Get Data, which reads JSON and other web formats, then shape the response in Power Query.
- CSV or SFTP: drop standardized files into a folder that a data gateway can see, and Power BI loads them like any other file source.
- Storage mode: for most energy portfolios, Import mode is the right default; use DirectQuery only when data volumes or freshness requirements truly call for querying the source at report time.
For automatic updates, Power BI's scheduled refresh lets you keep the model current. On shared (Pro) capacity you can schedule up to 8 refreshes a day, at least 30 minutes apart; on Premium, Premium Per User, or Fabric capacity you can schedule up to 48 a day. Utility data lands on billing and interval cycles, not by the minute, so a daily or twice daily refresh is usually plenty.
Point EZGB at your accounts and pick the feed that fits your setup at ezgb.ca/pricing, and your Power BI refresh becomes a scheduled job instead of a monthly manual export.
Example measures to build
Once the model is clean, the measures that used to feel out of reach become straightforward. A few that portfolio and sustainability teams tend to want first:
| Measure | What it answers | Built from |
|---|---|---|
| Total consumption | How much energy or water a site or portfolio used in a period | Sum of the meter reads fact table |
| Total cost | What that consumption cost, including delivery and taxes | Sum of the bills fact table |
| Cost per square foot | Which buildings are expensive to run for their size | Total cost divided by gross floor area from the site dimension |
| Energy use intensity (EUI) | Energy per unit of floor area per year, so buildings of different sizes compare fairly | Annual energy divided by gross floor area |
| Demand peaks | When and where load spiked, which drives demand charges | Max of demand from interval or billed demand |
| Blended rate | Effective price paid per unit | Total cost divided by total consumption |
Energy use intensity is worth calling out because it is the standard way to compare buildings. ENERGY STAR defines EUI as a building's annual energy use divided by its gross floor area, expressed as energy per square foot (or per square metre) per year, with a lower EUI generally signalling better performance. With floor area sitting in your site dimension and energy in your fact table, EUI is a one line measure.
A workable rollout
- Confirm the accounts and meters in scope, and where each one gets its data (utility connection, Green Button, or PDF bills through BillScan).
- Choose a delivery method: REST API for a live pull, or CSV over SFTP for scheduled file drops.
- Build the star schema: two fact tables at consistent grains, plus date, account, site, and utility dimensions.
- Set up scheduled refresh against the API or the gateway folder.
- Write your first measures (cost per square foot, EUI, demand peaks) and publish.
Book a walkthrough at ezgb.ca, tell us which accounts and commodities you track, and we will help you stand up a clean feed so your first Power BI energy dashboard ships in days rather than quarters.
Sources
- Understand star schema and the importance for Power BI (Microsoft Learn)
- Configure scheduled refresh (Microsoft Learn)
- DirectQuery in Power BI (Microsoft Learn)
- Web connector (Microsoft Power Query docs)
- What is Energy Use Intensity (EUI)? (ENERGY STAR)
- Green Button Standard (Green Button Alliance)
- Ontario energy data regulation requires utilities to implement Green Button (Green Button Alliance)
Source: learn.microsoft.com