by Wyn Hopkins

I recently presented a session at the Global Excel Summit in London entitled “Why Power BI is better than Excel … or is it?” Tough crowd!

My co-presenter Sue Bayes and I were up against our Unpivot co-hosts Giles Male and Mark Proctor in this light-hearted, somewhat tongue in cheek debate. And the outcome? “It Depends”.

I love Excel. I have spent years building complex financial reports, models and tools in it, I hold MVP status for it, and it is something I still use daily. So when I say I prefer building reports in Power BI, it is not a sales pitch. It is what I have learned from doing the work.

The part most people discover eventually when building reports

Building a good reporting solution takes time. Not because of the software, but because of the problem underneath it. You have to understand the data, the business logic buried inside it, and what the people reading the report actually need to see. That last one is the hard part, because most people do not really know what they want until something is sitting in front of them. That is not a criticism of them. It is just how we all work.

Which leads to a number that surprises people: in my experience, roughly 80% of the time on any reporting project goes into data preparation and modelling. Cleaning, transforming, connecting, and defining the logic that turns raw data into numbers a business can trust. The charts at the end are the relatively easy bit.

This is why the idea of a semantic model is so central. A semantic model is the business logic layer that sits between your raw data and your reports. It is where “Revenue” gets defined for your business, where year on year growth is calculated, where entities are mapped to cost centres. Get that layer right and reports become fast and flexible to build. Get it wrong, or skip it, and every new request becomes another complicated, time-consuming rebuild.

Power BI is built around this tabular model architecture.

Why this should matter to a CFO

It is easy to treat all of this as plumbing that lives below the CFO’s pay grade. It is not.

A well-structured semantic model organises your data into three simple ideas. Facts are the numbers you care about: sales, costs, transactions, units, the things you add up. Dimensions are the context you slice them by time, product, region, customer, account, business unit. Relationships are the wiring that connects the two, so a single cost figure knows which month, which region, and which cost centre it belongs to.

Get those three things right and something powerful happens. The model stops being a report and becomes a foundation. You can ask it questions it was never explicitly built to answer, because the facts, dimensions and relationships are already in place. A new report, a new angle, a new slice, the structure already supports it.

For a CFO that translates into a few things that matter. A single, trustworthy source for decisions, where the numbers reconcile because they come from one defined model rather than a dozen competing spreadsheets. Flexible reporting that keeps pace with the questions the business is asking now, rather than the questions it asked last quarter. And quicker decisions, because the gap between a question and a credible answer shrinks from days to minutes.

There is a longer game here too. The same well-structured model that powers your reports today is the foundation that AI driven analysis will depend on tomorrow. AI is only as good as the data underneath it. A clean semantic model, with clearly defined facts, dimensions and relationships, is exactly what makes AI driven analysis and decision assistance trustworthy rather than a guessing machine. Build the foundation properly now and you are not just solving this year’s reporting; you are laying a critical pillar for the analysis and decision support that comes next.

A short techy history lesson: Power Pivot came first

If you have used Excel’s Power Pivot feature, you already understand the idea of a semantic model living inside a spreadsheet. Power Pivot was a real leap forward when it arrived. It brought a SQL Server Analysis Services engine into Excel, and we helped plenty of finance teams build serious analytical capability on top of it.

Its super-power, when combined with Power Query, was two-fold:

1. Handling a lot of related data easily with single-click updates.
2. Speed of iteration when changing the presentation layer – just drag and drop a different dimension or calculation (measure) and hey presto!

Power BI grew straight out of that same technology. The DAX formula language, the columnar engine, the relationships between tables, all of it traces back to Power Pivot.

The difference is what happened next. Power Pivot has stood still in the past decade. Microsoft put its investment into Power BI instead. The two tools share the same DNA, but one of them has kept evolving while the other has largely stagnated.

If you have built in Power Pivot, moving to Power BI will feel familiar. The concepts carry over, and the DAX you already know still works. What you gain is everything Microsoft has added to Power BI in the years since Power Pivot stopped keeping pace.

Why Power BI wins for the report creator

1. Changes do not break everything

This one is the mic drop for me. It is worth dwelling on, particularly if you have ever lived through a Power Pivot restructure.

In Excel, when you are using Power Pivot, renaming a table or restructuring the model can set off hours of repair. Broken references, formula errors, pivot tables that need re-mapping, charts that need rebuilding. A change to the underlying model can break relationships and measures that require time consuming fixes.

In Power BI, when I rename a table in the semantic model, that change flows through the report and every measure built on top of it. One edit, and the rest updates. For finance teams whose structure keeps shifting, new entities, new cost categories, reorganised hierarchies, that robustness is not a nice extra. It is the thing that keeps the reporting alive between restructures.

2. Slicers, filters, and a canvas that goes where you want

Excel slicers do the job. Power BI slicers do it better, and there are far more of them. Dropdown lists for compact filtering. Search as you type for long dimension lists. Relative date slicers such as “last 3 months” or “this financial year”. Hierarchy slicers for drilling through an org or product structure. Each one can be placed, sized, and styled on its own.

And the canvas itself matters. In Excel your pivot table sits in the grid, and the layout becomes a set of compromises around that grid. In Power BI the canvas starts blank and is freeform. A matrix visual, which is the Power BI equivalent of a pivot table, goes wherever makes sense for the reader rather than wherever the data structure forces it.

3. A calculation engine that keeps getting more powerful

Power BI and Power Pivot share the same DAX language at their core, and this is another place where the investment gap between them shows.

Power BI keeps receiving new DAX functionality that Power Pivot does not have. For example:

Calculated tables let new data structures be generated inside the model using DAX, such as summary tables, date scaffolds, or what-if parameter tables, without going back to the source or building something in Power Query. The logic stays in one place and the model stands on its own.

Visual calculations are a newer and significant addition. Calculations like running totals, rankings, and period over period comparisons used to require fiddly DAX that was easy to get wrong and hard to maintain. Visual calculations allow simpler logic that works directly on what is visible in a chart or table, without wrestling all the context rules that make traditional measures tricky. Fewer errors, faster work.

4. Visuals without limits

Every reporting tool ships with a standard set of charts: bar, line, scatter, pie, table. Power BI is no exception, and its built in visuals are good. But there is a ceiling, and finance teams hit it often. The waterfall that has to look a particular way. The variance chart that follows IBCS standards. The KPI card that needs precisely the right information in precisely the right layout.

Power BI gets past that ceiling in two ways.

SVG images generated by DAX let custom visual elements be built right into your report.

The Deneb custom visual brings the Vega-Lite visualisation grammar into Power BI allowing someone with a little experience ( or an AI agent ) to build the exact visual you need.

You are not limited to what comes in the box. If you can describe what a visual should show and how it should look, Power BI can build it. The limit is skill and imagination, not the software.

Why Power BI wins for the report consumer

1. Intuitive cross filtering of all elements on a page

This is one of the features that changes how people work with their numbers, and it is hard to appreciate until you have used it.

In Power BI, the visuals on a page talk to each other by default. Click a bar in one chart and everything else on the page filters to match: the table beneath it, the cards beside it, the trend line in the corner. You are not running a new query or setting a filter. The page responds straight away and narrows to exactly what you clicked.

For a CFO reviewing the month, that means spotting something odd in a chart, clicking it, and immediately seeing which division or product or cost centre is behind it. No new report, no separate filter to apply. The data follows your curiosity.

Here’s an example of my home power usage, you can click and play and try to work out why my usage spikes at 6.30 am!

2. User driven chart elements (aka Field Parameters)

These let the reader switch what a chart or table shows, swapping between metrics, dimensions, or time periods, with no separate page built for each combination. A CFO who wants to flip between gross margin, EBITDA, and net profit in one view, or move a chart between monthly and quarterly, can do it themselves rather than asking for a new report.

The payoff is the same. Faster iteration, and less time between “can we see it another way?” and actually seeing it that way.   Here’s my video when Field Parameters were originally announced.

3. Right click drill through: from summary to customised detail in one step

Drill through lets you right click any data point and jump to a detail page scoped to exactly that context. Now, to be fair, you can right click on a pivot table value in Excel and show detail, which creates a new sheet of transactions, but you have no control over what’s displayed and each created view then needs to be deleted.

In Power BI, if you are looking at a regional sales summary, you right click one region, and a detail page that has been carefully crafted in advance opens showing every transaction, every customer, every line item for that region, already filtered, alongside any charts you built into that detail page. When you are done you step back to the summary.

That closes the gap between the high-level view and the underlying detail finance teams always end up needing. Instead of bouncing between a summary file and a transaction export, it is all in one place.

Benefits of publishing a Power BI report online (to the service)

Power BI Desktop is the development surface, and when you are ready to share your report you publish it to your own PowerBI.com site.

Publishing a Power BI semantic model to the service is not just moving a file to a shared folder. It connects your model to proper reporting infrastructure, and that opens a new layer of capabilities. Three stand out.

1. A shared source of truth

One semantic model, published once, can power many reports (including Excel reports) built by many people across the business. Everyone works from the same definitions, the same measures, the same logic. Update the model and every report built on it reflects the change. No more guessing which version of the file is the right one. While this has fantastic potential, the reality is it is rarely utilised in departmental reporting, and is more commonly the remit of large enterprise BI teams.

2. Row level security

You can define roles so different people see different slices of the data, by region, by entity, by cost centre, without building a separate report for each audience. The CFO sees everything. A regional manager sees their region. One report, governed access.

3. Scheduled refresh

Point the model at its source data, set a schedule, and reports update on their own, hourly or daily or whatever the business needs, with nobody opening a file and clicking refresh.

The Power Pivot middle ground, and why it is still not enough

Having covered what Power BI does well, there is one Excel approach that deserves more credit than it gets.

If your team has already built a solid Power Pivot model in Excel, you can publish that workbook to OneDrive or SharePoint, switch off the ability to download it, and give people a browser based view that is secure and needs no Power BI install. For a lot of smaller teams this is a legitimate and underrated setup. The model stays in Excel, the data stays in one place, and you skip the overhead of a full Power BI deployment.

But it has a ceiling, and you can reach it quickly. It cannot give you the cross visual filtering, shared model, the row level security, or the scheduled refresh I just described – the moment you need flexible exploration and filtering, governed access for different audiences, or data that refreshes on its own, the time to consider Power BI.

When I still use Excel

Excel is not dead, and should not ever die. Here are just a few of the scenarios where I still use it.

For financial modelling. Budget models, three way financial statements, scenario work. Excel is still the right tool. The formula transparency, the ability to trace a number back through its logic, the row and column structure that matches how accountants think, nothing replaces it here.

For data input and writeback. Anywhere people need to enter numbers, budgets, forecasts, manual adjustments, Excel is a simple option, these days however there are more options than ever such as Power Apps and Forms and clearly a CRM / Accounting System or CPM such as Solver Global should be the true system of record.

For self contained outputs. A one page summary emailed to a handful of people each month can be a well formatted spreadsheet. Sometimes that is plenty, and quicker to maintain.

For complex calculations that need to be easily reviewed and audited, for example a cost allocation tool or an attendance tracker.

Excel has a thousand uses and is the ultimate low-code platform, which is what makes it so resilient and useful.

The road ahead

Power BI does not stand alone, and that is worth understanding for any long term platform decision. Power BI is the reporting and analytics layer of Microsoft Fabric.

A useful way to think about it: Fabric is to Power BI what Microsoft Office is to Excel. Excel is powerful on its own, but it gets far more capable sitting inside a wider suite, sharing data with Word, connecting through Teams, living in SharePoint. Power BI inside Fabric works the same way. The report you build today connects to enterprise grade storage, automated processes, and a growing set of tools designed to work together.

One of the more interesting items on that roadmap is Fabric Planning. The lack of user input has always been a genuine Power BI limitation. If your team needs to enter budget figures, adjust forecasts, or write assumptions back, you have needed a separate tool to do it. Fabric Planning looks set to address that directly, bringing writeback and planning into the same ecosystem as your reports and your model. The detail is still taking shape, but the direction is clear. The split between reporting and planning, which has always meant stitching tools together, is starting to close.

For a CFO weighing up where to invest, that is the point. A semantic model built well today is not a dead end. It is the foundation for a broader data and planning capability that Microsoft is actively building out. The teams that understand the model and build it properly now will be the ones ready for what comes next.

The takeaway

Power BI did not appear from nowhere. It grew out of the same Power Pivot technology many finance teams already use, and then it kept evolving while Power Pivot largely did not.

The semantic model is still the foundation. Define your business logic once, properly, and reports built on top of it come quickly. The back and forth that starts the moment stakeholders see real numbers, “can we see it by region?”, “what is driving that spike?”, “let me into the detail on that one”, is exactly where an interactive, drill through, filterable Power BI canvas earns its place.

If you are a finance leader who wants reporting that is maintainable, scalable, and is quick to re-shape every time the business is restructured, Power BI is worth the investment.

Build the model right. The informed decisions follow.

Want to build smarter, more intuitive reporting tools for your business? Get in touch with the Access Analytic team — we’d love to help.