Benefits of Querying SharePoint Search:
Managed Metadata Support: When building our first reports we quickly ran into a wall where we could not obtain the values of managed metadata fields (TechNet). We could not add metrics from these field types to our reports however they were quite valuable from a BI standpoint. Querying list data via search provides Managed Metadata Field values.
Aggregate List Support: We needed to report on aggregate against several dozen project sites, with several primary content types. List REST service nor Web Services were a suitable candidate for performance reasons. By connecting Power BI to SharePoint Search we were able to aggregate data from several different project sites, appropriately security trimmed, and present this data in dashboards, reports and Excel workbooks.
Performance: While we did not track metrics, SharePoint Seach on Office 365 was significantly faster than the REST or Web Services APIs.
Connecting Power BI to SharePoint Search
Firstly, SharePoint Search returns at most 500 items per response. We want all records of a specific content type so we’re going to have to iterate through pages of data. Moreover Power Query does not support tail call elimination (recursion) and is functional in nature, so we need to build a list of functional queries and union / sort the results.
SharePoint Search REST API
In order to connect to SharePoint Data we will use the SharePoint REST API (supported by on premise & Office 365) as follows:
https://your-sp-site-url/_api/search/query?querytext='" & querytext & "'&trimduplicates=false&rowlimit=" & Number.ToText(rowsperpage) & "&selectproperties='" & fields & "'&startrow=" & Number.ToText(startrow)
querytext: the query being executed, we will input the parent Content Type ID here to aggregate information from all sites.
trimduplicates: false, we want all records returned.
rowlimit: numeric, the number of rows to return per page. We are using max 500 per page however this could be adapted differently.
selectproperties: comma separated list of Search Metadata Property names (make sure they are set to Retrieve data).
startrow: numeric, the first row of data to return.
The following methods perform the SharePoint Search Power Query:
1) GetFirstPageData: This method retrieves the first page of data. We are returning a record from this method so we can quickly check result count & generate our queries for subsequent pages of data.
(querytext as text, startrow as number, rowsperpage as number, fields as text) as record => let
Source = OData.Feed("https://your-sp-site-url/_api/search/query?querytext='" & querytext & "'&trimduplicates=false&rowlimit=" & Number.ToText(rowsperpage) & "&selectproperties='" & fields & "'&startrow=" & Number.ToText(startrow)),
PrimaryQueryResult = Source[PrimaryQueryResult],
RelevantResults = PrimaryQueryResult[RelevantResults],
ReturnTable = PrimaryQueryResult[RelevantResults]
in ReturnTable
2) GetData: This method retrieves subsequent pages of data. This returns lists of search result for use in union with previous pages returned.
(querytext as text, startrow as number, rowsperpage as number, fields as text) as list => let
Source = OData.Feed("https://your-sp-site-url/_api/search/query?querytext='" & querytext & "'&trimduplicates=false&rowlimit=" & Number.ToText(rowsperpage) & "&selectproperties='" & fields & "'&startrow=" & Number.ToText(startrow)),
PrimaryQueryResult = Source[PrimaryQueryResult],
ReturnTable = PrimaryQueryResult[RelevantResults][Table][Rows]
in ReturnTable
3) SearchSharePoint: This method executes the search process by iterating over and performing a union of search results. We’ve defined our field list and rows per page in this function however this could also be passed in as a parameter.
(querytext as text) as list => let
Fields = "Title,Path,Custom-Field-1,Custom-Field-2,SiteName,ModifiedOWSDATE,ModifiedBy,ContentTypeId",
RowsPerPage = 500,
Info = GetFirstPageData(querytext,0, RowsPerPage,Fields),
TotalRows = Info[TotalRows],
FirstPage = Info[Table][Rows],
ResultRows = if Info[RowCount] < TotalRows then
let
Iterator = List.Generate(()=>RowsPerPage, each _ < TotalRows, each _ + RowsPerPage),
AllResults = List.Transform(Iterator, each List.Combine({FirstPage, GetData(querytext,_,RowsPerPage,Fields)})),
Results = List.Union(AllResults)
in
Results
else FirstPage
in ResultRows
4) DocumentSearch: This method coordinates the search process by executing search calls, transforming, sorting, and returning result data tables. We’ve extended this method to rename columns into a more usable search experience
let
Rows = SearchSharePoint("ContentTypeId:0x010100YOURCUSTOMCTXID*"),
AllRows = List.Transform(Rows, each _[Cells]),
RowsToTables = List.Transform(AllRows, each List.Transform(_, each Record.ToTable(_))),
SkelToList = List.Transform(RowsToTables, each Table.FromList(_, Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
CleanRows = List.Transform(SkelToList, each List.Transform(_[Column1], each Table.PromoteHeaders(Table.RemoveLastN( Table.RemoveColumns( _,{"Name"}), 1) ) ) ),
TransposeTable = Table.FromRows(List.Transform(CleanRows, each List.Transform(_, each Record.FieldValues(_{0}){0} ))),
ColumnRenames = List.Transform(CleanRows{0}, each { "Column" & Text.From( List.PositionOf(CleanRows{0}, _) + 1), Table.ColumnNames(_){0}}),
RenamedTable = Table.RenameColumns(TransposeTable, ColumnRenames),
DocumentSearchResults = Table.RemoveColumns(RenamedTable,{"RenderTemplateId", "piSearchResultId"})
in DocumentSearchResults
We hope you find this useful. If you’re getting started with Power BI and need some help don’t hesitate to reach out.
If you are looking to have this run on Power BI Online please see the follow-up work by Paul Keijzers at Connect Power BI Online to SharePoint Search using REST.
