---
title: "Build a SharePoint List-Connected Power BI Report (And Handle the 5,000-Row Problem)"
date: 2026-06-09T00:00:00Z
updated: 2026-06-14T05:05:49Z
tags: ["Power BI", "SharePoint", "Power Query", "Microsoft 365", "OData", "Query Folding", "Scheduled Refresh"]
canonical: https://bianca.codes/blog/sharepoint-list-power-bi-report-5000-row-problem/
---

# Build a SharePoint List-Connected Power BI Report (And Handle the 5,000-Row Problem)

_Connect Power BI to a SharePoint List the way that doesn't fall over at 5,001 rows - OData filtering at the source, an indexed filter column, and a refresh schedule that doesn't need a gateway._

You'll build a Power BI report connected to a SharePoint List, with the data brought in through Power Query, filtered _at the source_ using an OData query so that SharePoint only ever sends the rows you asked for, and refreshing on a schedule in the Power BI Service. The example list is an equipment checks register - one row per inspection, with a status, a date, and a technician - but the technique is identical for any list.

What you need to start: Power BI Desktop, a SharePoint Online list you can read, and the site URL. No gateway, no admin rights, no premium licensing. The whole build is about thirty minutes.

The reason this post exists, rather than just "click Get Data, pick SharePoint, done": SharePoint Online has a list view threshold of 5,000 items. Queries that sort or filter on a non-indexed column stop working once the list crosses that line, and lists have a habit of crossing it precisely when the solution has been running long enough to matter. We'll build this the way that doesn't fall over at 5,001 rows.

## Why this approach

The obvious route is the built-in SharePoint Online list connector. It works, and for a small list it's fine. But it retrieves the _entire list_ and then applies your Power Query filter steps locally, in memory, after everything has already crossed the network. Filter steps against the SharePoint connector don't fold back to the source. On 800 rows nobody notices. On 40,000 rows your refresh takes long enough to schedule around, and every month it gets slower, because the connector is faithfully downloading rows you're going to throw away.

The OData endpoint flips this. Every SharePoint list exposes a REST API, and a `$filter` and `$select` written into the URL are executed _by SharePoint_ before anything is sent. You get only the columns and rows you asked for. The catch - and this is the part most write-ups skip - is that once the list is past 5,000 items, SharePoint will only execute filters it can satisfy with an index. Filter on an indexed column and the query runs no matter how big the list is. Filter on a non-indexed one and you get the threshold error. Which is why step 1 is not about Power BI at all.

## Step 1: Index the column you'll filter on

Go to the list in SharePoint. Settings gear → List settings → Indexed columns → Create a new index. Pick the column your report will filter by - for the equipment checks list, that's `Status`.

Two timing rules worth knowing. You can add an index to a list of up to 20,000 items; past that, the door closes and your options get considerably worse. And SharePoint's automatic index creation doesn't operate above that line either, so don't count on it to have quietly solved this for you. If your list is under 20,000 items right now, index the column today, even if the report is a someday project.

Not every column type can be indexed - single line of text, choice, number, date, yes/no, and single-value lookups are all fine, but multi-line text and multi-value anything are not. If your intended filter column can't be indexed, that's a sign to filter on something else, like a date.

## Step 2: Connect with OData.Feed instead of the SharePoint connector

In Power BI Desktop: Get Data → Blank query → Advanced Editor, and write the query directly:

```m
let
    Source = OData.Feed(
        "https://yourtenant.sharepoint.com/sites/Operations/_api/web/lists/getbytitle('Equipment Checks')/items?$select=Title,Status,CheckDate,Technician&$filter=Status eq 'Open'"
    )
in
    Source
```

The URL is doing the real work. `getbytitle('Equipment Checks')` names the list - spaces and all, inside the quotes. `$select` names the columns, which keeps SharePoint from sending the dozen system columns you don't want. `$filter=Status eq 'Open'` is the source-side filter, and because `Status` is indexed, it executes happily regardless of list size.

Authenticate with your organisational account when prompted. If you need a date filter instead, the REST syntax is `$filter=CheckDate ge datetime'2026-01-01T00:00:00Z'` - dates wrapped in the `datetime''` literal, in UTC.

One constraint to know about: the URL has a limit of roughly 2,100 characters on the SharePoint side. You will not hit it with a sane `$select` and `$filter`. You will hit it if you try to enumerate forty columns by name, which is its own message about the report design.

## Step 3: Shape the result in Power Query

The OData feed comes back clean compared to the native connector, but three shaping steps are still worth doing.

Remove the columns you didn't ask for but got anyway - the feed includes a handful of metadata fields regardless of `$select`. Set the data types explicitly, especially the date column. And know that SharePoint stores datetimes in UTC and sends them as UTC; if your report needs local time, convert it deliberately:

```fsharp
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns", {{"CheckDate", type datetimezone}}),
#"Local Time" = Table.TransformColumns(#"Changed Type", {"CheckDate", DateTimeZone.ToLocal})
```

The reason to be deliberate: the SharePoint web UI converts to your browser's timezone when it displays the list, so the list and your report can show different days for the same item if you skip this, and that's a fun conversation to have with a stakeholder at 9am.

## Step 4: Build the report

Close & Apply, then keep the report honest about what a list like this can answer. A card with the count of open checks. A bar chart of checks by status. A table of overdue items, sorted by date. The point of this post is the plumbing, not the layout, so build what your stakeholders actually asked for - and if what they asked for involves relationships between multiple lists, read the Between the Sheets series first, because that's a data modelling decision, not a connection setting.

## Step 5: Publish and schedule the refresh

Publish to the Power BI Service. In the workspace, find the semantic model → Settings → Data source credentials, and sign in with OAuth2 / your organisational account. Because SharePoint Online is a cloud source, there is no gateway involved - this is the single biggest quality-of-life difference from reporting on files sitting in someone's network drive.

One quirk: because your connection URL contains a query string, the Service's credential test can fail even when the credentials are right. Tick **Skip test connection** when you save the credentials. Then set the refresh schedule under Scheduled refresh - for an equipment checks list, daily at 6am is plenty.

Refresh once manually, confirm the row counts look right, and you're done.

## Common mistakes

### **Filtering on a column nobody indexed.**

The query works in the data preview, works on the first refresh, works for months - and then the list crosses 5,000 items and the refresh fails with a threshold error, having changed nothing. This is the defining failure mode of SharePoint as a source: the breakage is delayed until the data accumulates. The fix is the index from step 1, and the time to apply it is before 20,000 items, after which you can't.

### **Filtering in Power Query and assuming it folds.**

A filter step added in the Power Query UI against the native SharePoint connector does not become a source-side filter. It downloads everything, then discards. The report produces identical results either way, which is what makes this mistake invisible - the only symptom is a refresh duration that grows with the list instead of with your data. If the filter isn't in the URL, SharePoint never heard about it.

### **Pointing at a person or lookup column and getting a number back.**

Person and lookup columns don't store the value you see in the browser; they store an ID pointing at it. Through the OData feed you either expand the related entity in the URL (`$expand=Technician&$select=Technician/Title`) or you accept the ID. If a column came back as a cryptic integer, this is why. Plan for it rather than discovering it in the data preview - multi-value columns are worse again, and are a good reason to keep reporting columns simple in the list design.

## Frequently Asked Questions

### **Do I need a gateway to refresh a SharePoint list?**

No. SharePoint Online is a cloud source, so the Power BI Service connects to it directly with the OAuth2 credentials you store against the semantic model. Gateways are for data that lives on-premises. If someone has told you that you need a gateway for this, they're thinking of SharePoint Server, which is a different connector and a different life.

### **Should I use the v1.0 or v2.0 SharePoint connector if I go the native route?**

2.0, which has been generally available since September 2022 and uses better APIs. Be aware it inherits SharePoint's limit of 12 lookup-type columns per query - person, lookup, and managed metadata columns all count - and queries touching more than that will fail. The connector also offers an All view versus Default view choice; Default respects whatever view you've configured on the list, which is a reasonable way to pre-trim columns.

### **Does the 5,000 threshold mean my list can only hold 5,000 rows?**

No. A SharePoint list can store up to 30 million items. The 5,000 figure is the list view threshold - a per-query ceiling on what SharePoint will scan without an index. Storage is not the problem; retrieval is. That distinction is also why the threshold surprises people: nothing warns you at item 5,001, only at the first unindexed query after it.

### **Why is my refresh still slow even with OData filtering?**

Volume and width. If the `$filter` still matches 25,000 rows, those rows still have to travel. Tighten the filter to the reporting window you actually need, trim the `$select` further, and check you're not expanding multiple lookup columns, which multiplies the work. If you genuinely need the full history of a large list refreshed frequently, you've outgrown the source - that's tomorrow's Hot Take.

### **Can I do this with Microsoft Lists instead of SharePoint?**

You already are. Microsoft Lists is the same thing as SharePoint lists with a friendlier front door - every List lives in a SharePoint site and exposes the same REST API. The connection string, the threshold, and the indexing behaviour are all identical.

## Where to go from here

This post shows you how to connect to a SharePoint list properly. It deliberately doesn't argue about whether you should - that's the [Jun 10 Hot Take](#), which is about the limits you just engineered around and what they mean for the lifespan of this solution. And if the report you're building needs more than one list joined together, stop and read [Star Schema Done Right, Part 1](#) (4 Jun) first, because the modelling decisions matter more than the connector.