Building a Power BI Semantic Model from a SharePoint List

Half the data in an average Australian organisation does not live in a database. It lives in a SharePoint list. Project trackers, asset registers, leave requests, risk logs, supplier lists, the spreadsheet someone "upgraded" to a list three years ago. These lists run quietly in the background, people update them by hand, and at some point a manager asks whether they can get a dashboard off the back of one. The answer is yes, and Power BI makes it genuinely easy. But easy and wise are not the same thing, so let me walk through how it works and when you should actually do it.

Power BI lets you create a semantic model directly from a SharePoint list. A semantic model, if the term is new to you, is the data layer that sits underneath your reports: the tables, the relationships, the measures, the bit that turns raw rows into something you can slice and chart. Connecting a list to that layer means you can report on list data without exporting anything to Excel and without anyone copying and pasting.

How it actually works

You start in the SharePoint list itself. Modern SharePoint Online lists have an "Integrate" or "Export" option in the command bar, and from there you can push the list into Power BI. SharePoint does a chunk of the work for you: it auto-generates a starter report and creates the semantic model behind it, mapping the list columns to fields automatically.

Alternatively you go the other way, starting in Power BI Desktop and connecting to the list as a data source. There are two connectors that look almost identical and trip people up constantly. One is the older "SharePoint Online List" connector, and the other is the newer "SharePoint Online List" connector built on a different API (the v2.0 implementation). The newer one generally gives you cleaner data types and better column handling, especially with the more awkward column types. If your fields are coming through with weird names or missing data, the connector version is the first thing I check.

Once connected, the list columns become fields in your model. Text columns, numbers, dates, choice columns, they all map across. You can then build relationships to other tables, add calculated columns and measures, and treat it like any other Power BI model.

Where this is genuinely the right move

I am not a SharePoint-list-as-database evangelist, but there are situations where this is exactly the right tool and anything heavier would be overkill.

Small, human-maintained reference data is the sweet spot. A list of project statuses, a register of company assets, a roster, a controlled vocabulary that a handful of people update. This data is naturally low-volume, it changes through manual edits, and SharePoint already gives you a decent editing experience with forms and permissions. Building a separate database and an admin app to maintain it would be a waste when the list already does the job.

It is also a sensible move when SharePoint is already the source of truth and you do not want to fork the data. If the business genuinely manages something in a list and that is where the real, current version lives, reporting straight off it means your dashboard reflects reality rather than a stale export. We see a lot of teams running risk registers and project trackers this way, and connecting Power BI to the live list beats the old habit of someone emailing around a screenshot of a chart once a week.

For these cases, getting a clean model and a few well-built measures on top of a list is quick, and the payoff is real. This is the bread-and-butter stuff our Power BI consulting team sets up so that the rest of an organisation can self-serve.

Where it falls down

Here is the honest part, because this is where people get burned.

SharePoint lists have throttling and item limits. The list view threshold sits at 5,000 items, and while you can work around it, large lists become slow and fiddly to query. If your list has tens of thousands of rows and is growing, you are using the wrong tool. At that point the list is a database wearing a SharePoint costume, and you should move it into something built for the job (Dataverse, SQL, a Fabric Lakehouse) before you build reporting on it. I have seen lists with hundreds of thousands of rows that someone kept feeding because it "still worked", and the refresh times were brutal.

Refresh is the next gotcha. A semantic model built from a SharePoint list does not update live. It refreshes on a schedule, and for cloud-hosted SharePoint Online lists that scheduling is straightforward, but you still need to set it up and understand that your dashboard is as current as the last refresh, not the last edit. People assume "connected to SharePoint" means "live", and then wonder why a change they made five minutes ago is not in the report.

Complex column types need care. Lookup columns, person/group columns, managed metadata, and multi-select choice fields do not always come across cleanly. They often arrive as nested records or with extra fields you have to expand and tidy in Power Query. This is manageable, but it is not the one-click experience the marketing suggests, and it is exactly the kind of fiddly work that eats an afternoon if you have not done it before.

And calculated columns defined inside SharePoint can behave unpredictably when pulled into Power BI. My general advice is to do your calculations in the Power BI model with DAX rather than relying on SharePoint's calculated columns, because you get more control and the logic lives in one place.

A few hard-won tips

Use the newer connector unless you have a specific reason not to. It handles data types better and saves you cleanup.

Keep the list lean. Only pull the columns you actually need into the model. A list with forty columns where you report on six is just dragging dead weight through every refresh.

Think about permissions early. Power BI accesses the list using a set of credentials, and whoever or whatever those credentials belong to needs read access to the list. If your list is locked down with tight item-level permissions, the model sees what those credentials can see, which can produce confusing gaps if you do not account for it.

Decide upfront whether the list is the long-term home. If the data is going to grow or get more important, plan the move to a real data store now rather than rebuilding your reports later. The reporting layer can stay; the source underneath it should be the right one.

Where it fits in a bigger picture

Connecting Power BI to a SharePoint list is a great example of using a tool for what it is good at and not pretending it is something more. For the right data, low-volume, human-maintained, already living in SharePoint, it is fast, clean, and saves everyone the export-and-email dance. For the wrong data, high-volume or fast-growing, it is a trap that feels fine until it does not.

If you are trying to work out whether your SharePoint data should stay where it is or move into a proper data platform, that judgement call is exactly the kind of thing we help with. Have a look at how we approach business intelligence work, or get in touch and we will give you a straight read on your specific setup rather than a generic answer.

Reference: Create a semantic model from a SharePoint list, Power BI documentation.