The SharePoint API that's being used by the connector has a limit of 5000 rows. From what I understand, there are some ways around this.
1. You can use incremental refresh with a date column.
2. You can make multiple queries, each with different parameters (e.g. Query1 pulls row 1-4999, Query2 pulls row 5000-9999, etc..) - Basically manual pagination through multiple queries
3. Make a Power Query which returns everything using pagination (Example code is below)
4. Lastly you can make a function (just use chatgpt) to dynamically call the API as many times as is needed and then return back the results in 1 query.
Number 3's Code (Copy and paste this into a blank query in advanced editor - then replace the site url and the list name) :
let
// Base site and list
SiteUrl = "https://yourtenant.sharepoint.com/sites/yoursite",
ListName = "YourListName",
// Build base REST URL
BaseUrl = SiteUrl & "/_api/web/lists/getbytitle('" & ListName & "')/items?$top=5000",
// Define function to get one page
GetPage = (url as text) =>
let
Source = Json.Document(Web.Contents(url, [Headers = [Accept = "application/json;odata=nometadata"]])),
Data = try Source[value] otherwise Source,
NextLink = try Source[#"@odata.nextLink"] otherwise null,
Output = [Data = Data, NextLink = NextLink]
in
Output,
// Loop through pages
FirstPage = GetPage(BaseUrl),
PageList = List.Generate(
() => FirstPage,
each [NextLink] <> null,
each GetPage([NextLink]),
each [Data]
),
// Combine all pages
AllData = List.Combine(PageList),
TableData = Table.FromList(AllData, Record.ToTable, null, null, ExtraValues.Error),
Result = Table.ExpandRecordColumn(TableData, "Column1")
in
Result
