Getting Data from Excel Workbooks into Power BI - The Two Paths and When to Use Each

Every Australian business I've worked with runs on spreadsheets somewhere. Finance has a workbook that calculates the monthly numbers. Ops has a tracker that someone built in 2019 and now half the company depends on. Sales has a pipeline sheet that gets emailed around with "v7_FINAL_updated" in the filename. None of this is going away, and honestly, it shouldn't. Excel is a brilliant tool for the work it's good at.

The problem starts when you want those numbers in a proper report. Someone in leadership asks for a dashboard, and the data they want lives in three workbooks sitting in SharePoint. So you reach for Power BI, and the first question is: how do I actually get this Excel data in there? Turns out there are two completely different ways to do it, they behave nothing alike, and picking the wrong one is one of the most common mistakes I see when I review a client's Power BI setup.

So this post is the explainer I give clients before they start. What the two paths are, what each one is actually for, and where they bite.

Two paths that look the same and aren't

When you bring an Excel workbook into the Power BI service, you're choosing between two operations that the UI makes look almost identical. They are not.

Connect keeps the workbook as a workbook. Power BI shows you a live-ish view of the Excel file, rendered through Excel for the web, sitting inside the service. You're not pulling the data into a Power BI model. You're surfacing the spreadsheet itself, with its sheets, its PivotTables, its formatting, inside Power BI. People interact with it more or less the way they'd interact with the workbook in Excel Online.

Import is the other thing entirely. Here Power BI reaches into the workbook, reads the tables and the data model (if there is one), and pulls that data into a brand new Power BI dataset. From that point on, the workbook is just a source. You build reports off the dataset, not the spreadsheet. The Excel file and the Power BI report are now two separate things that happen to share an origin.

The reason this matters is that almost everyone clicks the wrong one the first time. They want a dashboard, so they should import. But "connect" sounds friendlier and less destructive, so they connect, and then they're confused about why they can't build a Power BI visual on top of it. Or it's the reverse. They've got a beautiful finance workbook with conditional formatting and they import it, then wonder where all the formatting went.

When to connect

Connect is the right call when the workbook itself is the deliverable. Finance has built a model in Excel, with all the formulas and the formatting and the structure they want, and they just need it accessible inside Power BI alongside everything else. They don't want it rebuilt. They want it as-is, viewable in the service, maybe pinned to a dashboard as a range.

This is genuinely useful and underused. I've had finance teams who spent years perfecting a workbook, and the worst thing you can do is tell them you're going to "modernise" it into a Power BI model that loses half the nuance. Connect lets them keep their workbook and still appear in the company's Power BI experience. You can even pin a selected range from the workbook to a Power BI dashboard, so a key summary table from the spreadsheet shows up next to your proper Power BI tiles.

The catch: the workbook needs to live somewhere Power BI can reach it. Typically that means OneDrive for Business or a SharePoint document library, not your local drive. If you upload a local file, you get a point-in-time snapshot that doesn't refresh. If the file lives in OneDrive or SharePoint, Power BI can keep the connection live and reflect changes. That distinction trips people up constantly, so check where your file actually lives before you promise anyone it'll stay up to date.

When to import

Import is what you want most of the time, because most of the time the goal is a real Power BI report. You want the data, you want to model it, you want relationships and DAX measures and interactive visuals. Once the data is in a dataset, you build properly. You can schedule refresh, apply row-level security, the lot.

If your workbook already contains a data model built in Power Pivot, import brings that model across, which is a nice shortcut for teams that started their analytics journey in Excel and are now graduating to Power BI. The Power Query queries, the relationships, the measures, a lot of that carries over. It's one of the better migration stories Microsoft has built.

The honest caveat is that import is only as good as the structure of your workbook. If your Excel data is in clean tables with proper headers, import is a delight. If your "data" is actually a formatted report with merged cells, blank rows for spacing, totals scattered through the middle, and a logo in cell A1, then import is going to be a fight. Power Query can clean a lot of it up, but you'll be doing real data-shaping work, and that's where a lot of DIY Power BI projects quietly stall. This is exactly the sort of thing we untangle in our Power BI consulting work, and nine times out of ten the fix is upstream: get the source data into a tabular shape, and everything downstream gets easier.

The OneDrive and SharePoint detail that everyone misses

Here's the bit worth tattooing on the back of your hand. Where the workbook lives decides whether refresh works.

A workbook stored in OneDrive for Business or a SharePoint Online document library can be refreshed by the Power BI service on a schedule, because the service can authenticate and go fetch the latest version. A workbook you've uploaded as a local file is frozen at the moment you uploaded it. No refresh. If finance updates the numbers and you uploaded a local copy, your report is wrong and nobody knows until someone notices the totals don't match.

So my standard advice: put the workbook in SharePoint or OneDrive first, then connect or import from there. Never import from a local file if the data changes. This one rule prevents a genuinely embarrassing category of "the dashboard is out of date and we presented it to the board" incidents.

What I actually recommend to clients

If the spreadsheet is a one-off, a quick look, a "can you just show me this in Power BI", and it's never going to update, fine, upload it locally and move on. Don't overthink the throwaway stuff.

But if this is going to be a recurring report that the business relies on, treat Excel as a temporary source, not a permanent one. Excel-as-a-database works until it doesn't, and the failure mode is ugly: someone renames a column, inserts a row, changes a sheet name, and your refresh breaks with an error that means nothing to the person who broke it. Spreadsheets are not designed to be machine-read on a schedule by another system. They're designed for humans to edit freely. Those two goals fight each other.

The pattern that holds up is to land the data somewhere structured first. A proper database, a Fabric Lakehouse, a dataflow that does the cleaning once and serves clean tables to everyone. We do a lot of this kind of architecture work, and the move from "Power BI reads the spreadsheet directly" to "Power BI reads a clean, governed source" is usually the single biggest reliability upgrade a reporting setup can get. If you're building out a broader analytics capability, our Microsoft Fabric consulting team spends most of its time on exactly this transition, getting businesses off the spreadsheet-as-source treadmill without throwing away the Excel work that actually matters.

The honest take

Getting Excel into Power BI is easy. Getting it in well is the part that takes judgement. The feature works, both paths work, and for a lot of small teams importing from a SharePoint-hosted workbook is a perfectly sensible place to start. I'm not going to tell you that's wrong. It's how plenty of good reporting setups begin.

What I will tell you is to be honest with yourself about whether the workbook is a deliverable or a database. If it's a deliverable that finance lovingly maintains, connect to it and respect it. If it's secretly being used as a database that feeds a critical report, you've got a ticking clock, and the sooner you move that data into something built to be read by machines, the fewer 7am "the numbers are wrong" calls you'll get.

If you want the mechanics straight from the source, Microsoft's documentation on getting data from Excel workbook files walks through both paths. And if you're staring at a tangle of workbooks wondering how to turn them into something trustworthy, that's daily work for us. Get in touch and we'll give you an honest read on whether you need a quick import or a proper rethink of where the data lives.