PowerBI and SharePoint 2013 Search Results

Article
06/14/2018

Knowing that the SharePoint 2013 Search API is all REST based, we can user PowerBI to retrieve the results of the REST call into a Data Set that can be used as the source of our PowerBI Dashboard or report.

REST Syntax

At the heart of any SharePoint search request is having a deep understanding of the REST syntax that you want to execute. This is fully documented for reference, however for this illustration we'll be using the following:

https://<SiteURL>/_api/search/query?querytext='contenttype:<ContentType>'&selectproperties='Path,Filename,Title,Author'

A few things to note:

The querytext parameter must use supported KQL query syntax.
The selectproperties querystring parameter must use managed properties in the SharePoint search schema. You can have as many as you like but they must be managed properties.

JSON Response

When we use the SharePoint Search API to execute a REST request the response is returned in an XML format similar to this:

The areas highlighted represent parent elements in the XML file. Knowing this will help you to navigate the DOM structure of the XML file. PowerBI will put each collection of nodes into separate lists and tables. When this happens we need to expand the results so we can use them in our data set.

PowerBI Data Set

Now that we have the JSON reponse in XML format, we need to configure the following steps in PowerBI to consume the JSON response. To properly parse the JSON response, several "helper" queries will need to be created.

Create a new parameter that will hold the REST request

From the Power BI desktop, the "Edit Queries" menu will bring you to a new screen where you can create and edit everything in this post.
Create a new parameter from the Manage Parameters option.

3. Create the new parameter and set the value to the REST query you want to execute.

Creating the base query

You will start with a blank query and put in the code necessary to transform the jSON response to a table that can be used.

1. In the blank query window you can then paste the following code. This will retrieve the jSON response from the REST call to the SharePoint search API.

let

Source = Json.Document(Web.Contents(RESTQuery,[Headers=[Accept="application/json;odata=verbose"]])),
d = Source[d],
query = d[query],
PrimaryQueryResult = query[PrimaryQueryResult],
RelevantResults = PrimaryQueryResult[RelevantResults],
Table = RelevantResults[Table],
Rows = Table[Rows],
results = Rows[results],
#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Cells"}, {"Column1.Cells"}),
#"Expanded Column1.Cells" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.Cells", {"results"}, {"Column1.Cells.results"}),
#"Expanded Column1.Cells.results" = Table.ExpandListColumn(#"Expanded Column1.Cells", "Column1.Cells.results"),
#"Expanded Column1.Cells.results1" = Table.ExpandRecordColumn(#"Expanded Column1.Cells.results", "Column1.Cells.results", {"__metadata", "Key", "Value"}, {"Column1.Cells.results.__metadata", "Column1.Cells.results.Key", "Column1.Cells.results.Value"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Column1.Cells.results1",{"Column1.Cells.results.Key", "Column1.Cells.results.Value"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Column1.Cells.results.Key", "Key"}, {"Column1.Cells.results.Value", "Value"}})

in
#"Renamed Columns"

2. When inserted the Advanced Editor should look like this:

3. What this is replicating are the steps that you can manually configure within Power BI. Use this to get started and then you can modify the steps to fit your specific needs like renaming columns or splitting out values from a column.

KeyValueCount Query

This is one of those "helper" queries that we'll need to transform the jSON response.

Create a new Blank Query like you did above and paste in the following code:

let
Source = SPSearch,
#"Grouped Rows" = Table.Group(Source, {"Key"}, {{"Count", each Table.RowCount(_), type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Count"}),
#"Counted Rows" = Table.RowCount(#"Removed Columns")
in
#"Counted Rows"

Transform the Search Results

We now do the final transformation on the jSON response to get it into a "table" format that we can easily use in Power BI.

Create a new Blank Query like you did above and paste in the following code:

let
Source = SPSearch,
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], KeyValue_Count)),
#"Removed Columns" = Table.SelectColumns(#"Inserted Integer-Division",{"Key", "Value", "Integer-Division"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Key]), "Key", "Value"),
#"Removed Other Columns" = Table.SelectColumns(#"Pivoted Column",{"Path", "Title", "Author", "RefinableString00"})
in
#"Removed Other Columns"

2. Note the "Removed Other Columns" line above. This is where you'll see your managed properties from the SharePoint search API. As you add managed properties to the selectproperties parameter of the REST call, they will automatically show up so you can use them in your reports and dashboards.

Display the Search Results

Now that we have all the ETL (Extract, Transform and Load) work done, we can use the SearchResult "table" in Power BI to create our reports and dashboards.

Some things to note:

The "Fields" pane on the right will display all of the managed properties returned by you're REST call to the Search API.
Focus on using the "SearchResults" source to build you're dashboards. The other "tables" are to assist in the ETL process.
You'll have the full Power BI experience as other data sources with the ability to drill and refine you're results for maximum analysis of your SharePoint data.
The full pbix file with all configurations can be downloaded from GitHub