Building energy and utility dashboards in Power BI from utility data

If you run energy or facilities for a Canadian portfolio, someone above you probably already asked for a Power BI dashboard. It is the reporting tool most of your finance and operations colleagues already have, so it becomes the default home for energy numbers too. The hard part is almost never the visuals. It is the data feeding them: bills that arrive as PDFs, meters in different units, months that never showed up, and estimated reads mixed in with real ones. This guide walks through how to build a utility data model in Power BI that people trust, and where the effort actually pays off.

Why Power BI became a common home for energy reporting

Three practical reasons keep energy reporting inside Power BI. First, the license is already paid for. Many organizations bought Microsoft 365 and Power BI for finance and operations, so putting energy data there means no new tool to procure or train on. Second, it sits next to the rest of the business: you can show energy cost beside budget, occupancy, or production in the same report. Third, it refreshes on a schedule, so a monthly board deck can stop being a manual copy and paste exercise.

The scale of what you are reporting on is not small. In 2019 there were 555,951 commercial and institutional buildings across the Canadian provinces, occupying 709.0 million square metres and consuming 948.2 million gigajoules of energy, at an average intensity of 1.31 gigajoules per square metre. A portfolio of even a few dozen sites generates a steady stream of meter reads and invoices that no one wants to reconcile by hand every month.

What a good utility data model looks like

Power BI works best on a star schema: one central fact table surrounded by dimension tables. For energy reporting, the fact table is your normalized record of meter reads and bill line items. Keep it long and narrow, one row per meter per period per commodity, rather than a wide spreadsheet with a column per month.

The fact table

A clean energy fact table usually holds these fields per row: a date (or billing period start and end), a meter key, a commodity (electricity, natural gas, water, steam), usage in a single standard unit, cost, and a flag for whether the read was actual or estimated. Everything else belongs in a dimension you join to, not in the fact table itself.

The dimensions around it

• Date dimension: a proper calendar table so you can do month over month, fiscal year, and same period last year comparisons.
• Site / building: floor area, building type, city, and climate zone. Floor area is what turns raw usage into intensity.
• Meter: commodity, unit, tariff class, and which site and account it belongs to.
• Weather: heating and cooling degree days by station and period, so you can normalize for a cold winter or a hot summer.
• Account / tariff: the supplier account and rate, useful when cost per unit swings and you need to explain why.

The garbage in problem

A Power BI energy dashboard is only ever as good as the utility data behind it. The visuals will render whatever you give them, which is exactly the danger: a clean looking chart built on dirty data is more misleading than no chart at all, because people act on it. These are the recurring problems energy teams hit.

• Mixed units: electricity in kWh, gas in cubic metres or GJ, water in cubic metres or gallons. Summed together they are meaningless.
• Missing months: a bill never arrived, or a meter was not read, and the trend line silently dips. Your year over year math is now wrong.
• Estimated reads: the utility estimated usage, then trued it up later. If you do not flag estimates, a correction shows up as a fake spike or a fake saving.
• PDF only bills: the numbers you need are locked in a scanned invoice. Manual re keying is slow and it introduces its own typos.
• Overlapping billing periods: bills rarely line up with calendar months, so a naive monthly sum double counts or drops days.
• Account and meter churn: a site changes suppliers or adds a meter, and history breaks unless the model tracks the change.

None of these are Power BI problems. They are data supply problems. You can paper over some of them with DAX, but the durable fix is to standardize, validate, and gap check the utility data before it ever reaches the model. That is upstream work, and it is where most of the value of a trustworthy dashboard is actually created.

Refresh options: getting data in and keeping it current

Power BI gives you two main storage modes, and the choice shapes how fresh your dashboard is and how much data it can hold.

Import vs DirectQuery

In Import mode, Power BI copies the data into the model, so reports are fast but you must refresh to pick up changes. In DirectQuery, the model holds only metadata and queries the source live, so users always see current data, but Microsoft notes DirectQuery carries a one million row limit on returned results and a query timeout, and it puts load on the source database. For most energy reporting, where data updates monthly or daily rather than by the second, Import mode with a scheduled refresh is the simpler and faster choice.

How often it can refresh

Refresh frequency depends on your license. Microsoft documents that Power BI Pro allows up to 8 scheduled refreshes per day, while Premium per user and Premium or Fabric capacity allow up to 48 scheduled refreshes per day. Model size matters too: a Pro semantic model is capped at 1 GB, which is plenty for years of monthly bill data but can be tight if you pull in high resolution interval reads for a large portfolio.

What to connect to

• CSV or Excel: the fastest way to start, and fine for a pilot, but manual file drops become the weak link once you have more than a handful of sites.
• An API or scheduled feed: a service delivers standardized rows on a cadence, so the refresh has something clean to pull without anyone touching a file.
• A data warehouse or lakehouse: the most durable pattern for a real portfolio. Land cleaned utility data in a database, point Power BI at it, and let scheduled refresh do the rest. This also lets other tools share the same numbers.

Example measures worth building

Once the model is clean, a small set of measures covers most of what energy and facility teams report on. Build these as reusable DAX measures, not as one off columns.

Energy use intensity is worth calling out because it is the standard way to compare buildings of different sizes. The US EPA, which runs ENERGY STAR Portfolio Manager, defines energy use intensity as a building's energy use divided by its floor area, expressed as energy per square foot per year. In Power BI, that is total usage from your fact table divided by floor area from the site dimension. It only produces an honest number when usage is in one unit and floor area is complete and current, which loops right back to the data quality point.

How MartinAI helps

MartinAI is a Canadian utility data platform that handles the upstream work a Power BI energy model depends on. It collects utility data from utility account connections, Green Button feeds, and scanned or PDF bills, then cleans and standardizes it: one unit per commodity, billing periods aligned, estimated reads flagged, and missing months surfaced instead of silently skipped. The result is a consistent fact table of meter reads and bill line items, with the site, meter, and account context already attached.

Because that clean data is delivered on a schedule, whether to a CSV feed, an API, or a warehouse table, your Power BI scheduled refresh always has trustworthy rows to pull. Your team stops re keying invoices and reconciling units, and spends its time on the measures and the decisions instead. The dashboard becomes something people act on because the numbers under it are sound.