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.
Different sources in Excel to import data
Within Excel there are three different places where you could use OData/web services to import data:
Here we have two options, one to import from SharePoint list and on to import from OData feed:
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
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.
SharePoint 2010 REST service url
Which method work?
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:
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.
Define your PowerPivot/transform your data in Excel and create your report.
Upload the Report to Office 365 and enable it in Power BI.
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!
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.
I get error when connecting to the Office 365 list from Excel using REST API. Despite of providing the valid username & pwd It says –
“We could’nt connect to the data source. Please check your logon information. Here’s the error message we got:
The remote server returned an error: (403) Forbidden”
How did you chose to authenticate when connecting to the list? Which rest service url did you use? Something like http://server/site/_api/lists/getbytitle('listname‘)/items or did you use the ListData.svc service?
I got the same error message when using the api/getbytitle option on a French tenant of Office365 Sharepoint list accessed from Excel 2016 Data Tab / Odata feed.
I’ve been browsing the web for days with no satisfying answer.