This post will describe how you can access an Office 365 list with Power BI using the Office 365/SharePoint REST API. This can be a very useful in a scenario where we have forms that stores data in lists or other information in our Office 365 environment that we would like to do reporting on. When I first tried to access an Office 365 list with Power BI using the REST API I was somewhat confused of which type of connection I should use in Excel/Power BI. In this guide I will show some of the pitfalls trying out the different options as well as the solution that works with Power BI and scheduled data refresh.

If you only want the solution scroll to the end of page.

Contents

Different sources in Excel to import data

Within Excel there are three different places where you could use OData/web services to import data:

In PowerQuery:

Here we have two options, one to import from SharePoint list and on to import from OData feed:

In PowerPivot:

In the data tab of Excel:

 

Office 365 REST Service URL

To make it a bit more confusing there are also different approaches to access list data within SharePoint 2013 and Office 365 REST servce. One is through the SharePoint 2013 newer REST service or using ListData.svc which still is there for backward compatibility to SharePoint 2010.

SharePoint 2013 REST service url

http://server/site/_api/lists/getbytitle('listname')/items

Note the /items at the end which tells the service that it is the list items we want and not just the list itself. Also you might want to change the url to use HTTPS.

More information:

https://msdn.microsoft.com/en-us/library/office/fp142380.aspx

SharePoint 2010 REST service url

http://server/site/_vti_bin/ListData.svc

More information:

https://msdn.microsoft.com/en-us/library/office/hh134614(v=office.14).aspx

Which method work?

PowerQuery with from SharePoint list – No

What would be natural to people I guess would be to use “From SharePoint List” using Power Query (which is also hardly promoted by Microsoft to user for Power BI). This was also what I tried first:

It does not work.

Note: It might be possible to get this working if you fiddle around in the advanced settings of the connection with the security settings. However this is not something we go in to here. We want something easy.

PowerQuery with OData Feed – No

When you do the connection you may user either the SharePoint 2010 or SharePoint 2013 REST API (they are both available in Office 365). After you entered the correct url (and make sure to use your organisational account to login when you get the configuration dialog) the data will load nicely into Power Query. So far so good. Yeah, you thought! When you upload it into the Power BI site, enabled the report and scheduled it for refresh you will get this:

And if you ignore the error and still try to refresh it you will get this:

 

The solution – OData Feed from Excel Data Tab

Use the Office 365/SharePoint 2013 REST API and change the URL/listname to match your environment:

http://server/site/_api/lists/getbytitle('listname')/items

Note: It will work with the older SharePoint 2010 ListData.svc REST service as well. However use this one since this is a newer approach and more future proof.

Step 1

Step 2

Step 3

Step 4

Step 5

Define your PowerPivot/transform your data in Excel and create your report.

Step 6

Upload the Report to Office 365 and enable it in Power BI.

Step 7

Enable the report for scheduled refresh and configure date/time.

Note that you will get the error message as seen in screenshot above when you test the connection – IGNORE IT!

Step 8

Save and refresh the report! Success we have now connected an Office 365 list with Power BI! It is a bit ridiculous that it is not more straight forward and easy to achieve this without error messages etc… Let’s hope Microsoft improve things like this in the next release of Power BI.