I’ve been using Power BI & SharePoint Search to aggregate and report on SharePoint project data recently. If you haven’t already read Connecting Power BI to SharePoint Search (for Managed Metadata & Aggregate List Support) it may be worth a read to get up to speed on the details of this particular power query.

We’ve uploaded an Excel Workbook to GitHub containing the SharePoint Search Power Query. You’re going to need to ensure you’ve signed up for Power BI at http://www.powerbi.com and connected with your O365 tenant. You also need to download and install the most recent version of Microsoft Power Query for Excel (Workbook created with Version: 2.24.4064.242).

Download Excel Workbook with SharePoint Search Power Query from GitHub

To use this workbook, enter all parameters, click on the Power Query Ribbon Tab, click the Show Pane button, and refresh the SearchResults Data Source, click on the SearchResults worksheet. The workbook takes three parameters:

Search Keywords: You can add any search expression here, it will be appended to the SP Search REST call. As all results will be returned please make sure to constrain this to a reasonable #.

Tenant URL: Your Office 365 Tenant URL (or URL to a specific site collection). You’re going to have to authenticate using your O365 Organizational Profile as follows:

Step 1) You may first be prompted to use Anonymous authentication. As you want to protect your organizations data, select Organizational Account at the bottom of the green bar on the left.

Step 2) Organizational account should now be highlighted. Click the Sign In button to open O365 Sign-In Screen.

Step 3) Sign in with your Office 365 Credentials.

Step 4) Set the privacy level of data in this workbook to Organizational and click Save.

Metadata Properties: These are the columns you would like to return in the search results. Columns should be comma delimited with no spaces.

I hope you enjoy the workbook. With Search being a huge cornerstone in O365, I’d love to hear more about how you’re using this in your unique Office 365 Scenario (Delve,Exchange,SharePoint etc.)!

If you need any help or want a Power BI Consult to see how you can unlock the power of data within your organization please 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.

Recent Insights