retrieve sharepoint search results with pagination in power bi
snippet
power platform & sharepointadded 14th Mar 2025
Use in Power BI to retrieve SharePoint search results using pagination to go over the default limit.
I'm not sure of the original source of this snippet and it's been slightly modified. This example gets the the recent and lifetime views of library files as it's easier to get this via the search results than using the normal SharePoint library APIs.
let
// The base search query to use.
// Update this to the right SharePoint site and query parameters.
BaseUrl = "https://yourtenant.sharepoint.com/sites/{sitePath}/_api/search/query?querytext='{queryText}'&selectproperties='{selectedProperties}'&trimduplicates=false",
RecordsPerPage = 500,
GetRecordCount = () =>
let
Url = BaseUrl & "&rowsperpage=1&rowlimit=1",
Source = OData.Feed(Url, null, [Implementation = "2.0"]),
PrimaryQueryResult = Source[PrimaryQueryResult],
RelevantResults = PrimaryQueryResult[RelevantResults],
Count = RelevantResults[TotalRows]
in
Count,
GetPage = (Index) =>
let
Skip = "&startrow=" & Text.From(Index * RecordsPerPage),
// Despite the documentation, just using rowsPerPage still limits results so we need to pass rowLimit.
Top = "&rowsperpage=" & Text.From(RecordsPerPage) & "&rowlimit=" & Text.From(RecordsPerPage),
Url = BaseUrl & Skip & Top,
Source = OData.Feed(Url, null, [Implementation = "2.0"]),
PrimaryQueryResult = Source[PrimaryQueryResult],
RelevantResults = PrimaryQueryResult[RelevantResults],
Table = RelevantResults[Table],
Rows = Table[Rows],
ToTable = Table.FromList(Rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
WithIndex = Table.AddIndexColumn(ToTable, "Index", 0, 1, Int64.Type),
ExpandedColumn = Table.ExpandRecordColumn(WithIndex, "Column1", {"Cells"}, {"Column1.Cells"}),
Cells = Table.ExpandListColumn(ExpandedColumn, "Column1.Cells"),
ExpandedCells = Table.ExpandRecordColumn(
Cells, "Column1.Cells", {"Key", "Value"}, {"Column1.Cells.Key", "Column1.Cells.Value"}
),
FilteredValues = Table.SelectRows(
ExpandedCells,
each
(
// Update this to filter just the SharePoint properties we want in the resulting table.
[Column1.Cells.Key] = "Title"
or [Column1.Cells.Key] = "ViewsLifeTime"
or [Column1.Cells.Key] = "ViewsRecent"
)
),
Pivoted = Table.Pivot(
FilteredValues,
List.Distinct(FilteredValues[Column1.Cells.Key]),
"Column1.Cells.Key",
"Column1.Cells.Value"
)
in
Pivoted,
RecordCount = List.Max({RecordsPerPage, GetRecordCount()}),
PageCount = Number.RoundUp(RecordCount / RecordsPerPage),
PageIndices = {0..PageCount - 1},
Pages = List.Transform(PageIndices, each GetPage(_)),
Results = Table.Combine(Pages),
FixType = Table.TransformColumnTypes(Results, {{"ViewsLifeTime", Int64.Type}, {"ViewsRecent", Int64.Type}})
in
FixType