Refresh a Power BI Semantic Model from an Excel Workbook in OneDrive or SharePoint

There is a version of this problem I see in almost every business we walk into. A spreadsheet holds numbers that matter, someone built a Power BI report on top of it, and the whole thing depends on that file staying put and staying reachable. When the file lives on a laptop, refreshes break constantly and you need a gateway to make it work at all. But when the file lives in OneDrive for Business or a SharePoint document library, the story gets a lot better. Power BI can reach into the cloud and pull the file on its own, no gateway required.

That is the setup this post is about. Microsoft's documentation on refreshing from a workbook in OneDrive covers the steps. What I want to add is the practical view: when this approach is genuinely good, where it quietly falls over, and how we set it up so it keeps working after the project ends.

Why cloud storage changes the game

The core problem with a local Excel file is that the Power BI service cannot see it. Your report was built against a path like C:\Users\dave\finance.xlsx, and the cloud has no idea what that path means. So you bolt on a data gateway, install it on a machine that has to stay awake, and hope nobody turns it off.

Put the same workbook in OneDrive for Business or a SharePoint library and that whole layer disappears. The Power BI service authenticates to Microsoft 365, reaches the file directly through the cloud, and refreshes the model without any on-premises component. No gateway to install, no server to babysit, no laptop-in-a-drawer failure mode. For a lot of Australian businesses already running Microsoft 365, this is the path of least resistance and honestly the one I would push you toward.

There is a second thing cloud storage gives you that people underrate. When the workbook lives in OneDrive or SharePoint, Power BI keeps the model roughly in step with the file automatically. Roughly every hour, the service checks whether the workbook has changed and updates the model to match. Someone edits the numbers in the morning, and by mid-morning the report reflects them without anyone clicking anything. That is a genuinely nice property when the workbook is the source of truth and gets edited by hand.

Connect versus import, and why it matters

Here is a fork in the road that trips people up. When you point Power BI at an Excel file in OneDrive or SharePoint, you get two very different options, and they behave nothing alike.

The first is to import the workbook's data into a semantic model. Power BI reads the tables, the data model, and any Power Query you built inside Excel, and it builds a proper Power BI model from them. This is what you want when the report is the destination and the workbook is just the data source. You build visuals in Power BI, and the workbook feeds them.

The second option keeps the Excel experience itself, so you can see and interact with the workbook inside the Power BI service, PivotTables and all. That is a different tool for a different job, and it is not what most people mean when they say they want a report that refreshes.

I mention this because I have watched people pick the wrong one, wonder why their Power BI visuals will not build the way they expect, and burn an afternoon on it. If you want a Power BI report that updates from the workbook, you want the import-into-a-semantic-model path. Get that choice right at the start and the rest goes smoothly.

This foundational stuff, choosing the right connection type and knowing why, is exactly where a bit of experience pays for itself. It is the kind of thing our Power BI consultants sort out in the first hour rather than the third week.

The credential and location details that decide everything

Once you are on the cloud-refresh path, reliability comes down to a few unglamorous settings. Get them right and the report refreshes quietly for years. Get them wrong and you are back to explaining stale numbers in a meeting.

The account matters. The Power BI service refreshes the file using OAuth credentials tied to a Microsoft 365 account, and that account needs to actually have access to the OneDrive or SharePoint location where the file lives. If the workbook sits in a personal OneDrive owned by someone who then leaves the company, their account gets deprovisioned and the refresh dies with it. I have seen exactly this: a critical report that stopped updating because it was quietly hanging off the OneDrive of a person who had left three months earlier, and nobody connected the two facts until the numbers were badly out of date.

My blunt advice is to keep workbooks that a team depends on in SharePoint, in a document library owned by the team or the department, not in one individual's personal OneDrive. SharePoint outlives staff turnover. Personal OneDrive does not. This one decision removes a whole category of future pain.

There is also the matter of moving or renaming the file. The link between the model and the workbook is based on the file's location. Move the workbook to a different folder, rename it, or restructure the library, and the refresh loses the thread. So agree on where the file lives before you build the report on it, and then leave it alone. A tidy-up of the SharePoint site six months later is a classic way to silently break a dashboard nobody was watching closely.

What still trips people up

I want to be honest about the rough edges, because the "no gateway needed" story makes this sound frictionless and it mostly is, but not entirely.

The automatic sync is periodic, not instant. If someone edits the workbook and then stares at the report expecting the change immediately, they will be disappointed, because the service checks on its own schedule rather than the moment you save. For scheduled reporting this is fine. For anyone expecting live, second-by-second updates, it is the wrong tool and you should set that expectation early.

The structure of the workbook still matters as much as it ever did. Cloud storage fixes the reachability problem, it does not fix the fragility of Excel as a data source. If someone inserts a column, renames a tab, or reshapes a table, the model can break in ways that are annoying to diagnose. I once watched a finance dashboard fall over because a well-meaning person sorted a column in the source sheet and shifted the data into a shape the model did not expect. Excel is forgiving to the human editing it and unforgiving to the model reading it. That truth does not change just because the file moved to the cloud.

And credentials expire or get revoked. OAuth tokens need re-authentication now and then, especially after password changes or conditional-access policy updates, which are common in security-conscious Australian organisations. When a refresh suddenly fails on a report that has been fine for months, a lapsed credential is the first place I look. Re-sign-in usually fixes it in a minute, but only if you know to check.

My honest take

Refreshing Power BI from an Excel workbook in OneDrive or SharePoint is one of the genuinely good setups in this space, and if you are going to keep data in Excel, this is close to the best way to do it. No gateway, automatic sync, and it leans on the Microsoft 365 you are probably already paying for. When a client tells me they have an Excel-backed report and the file is on a laptop, moving that file to SharePoint is often the single highest-value change I can suggest, and it takes about ten minutes.

That said, I will give you the same caution I give every client. If the numbers in that workbook are important enough to report on and refresh regularly, it is worth asking whether Excel should be the source at all. A file one person edits by hand, that can be broken by a stray sort or a renamed tab, is a shaky foundation for anything the business leans on. Sometimes Excel is the pragmatic reality and the job is to make refreshing from it as solid as possible. Other times the honest answer is that the data belongs in a proper store, and the workbook is a habit rather than a requirement.

For the genuinely important data, moving it into a real source, whether that is a database or a Fabric lakehouse, changes the reliability picture entirely. That is a data platform conversation rather than a refresh one, and it is the sort of thing we work through as part of business AI strategy, because clean, dependable data is what every analytics and AI effort quietly stands or falls on.

Where I would start

If you have a report that refreshes from a cloud Excel file and it keeps failing, work through it in order. Is the workbook in SharePoint or a team OneDrive rather than a personal one owned by someone who might leave? Did you pick the import-into-a-semantic-model path rather than the keep-the-Excel-experience one? Are the OAuth credentials current, and did anything change recently with passwords or access policies? Has the file been moved or renamed since the report was built? Most broken cloud refreshes fail on one of those four.

Fix them and you get a refresh that just works, quietly, week after week, which is all anyone actually wants. If you would rather someone set it up properly and hand it back working, that is bread-and-butter work for us. Take a look at our data and analytics services or get in touch and tell us what you are wrestling with.