Getting Up and Running with Power BI and SharePoint Lists
A few weeks back when I was at the Digital Workplace Conference in Melbourne – a most excellent conference – I saw Adam Cogan (@adamcogan) demonstrate Power BI in his session Improve Your Business Intelligence with Power BI. I’d seen Power BI before a few times, but the way Adam showed it, it finally “clicked” for me. I may be hooked, and I *know* I’m going to be able to do some amazing things for clients with it.
I had to try it out. I have a client where I think Power BI could be an excellent part of the over all solution, so I tried a few things out in their tenant. The client is a pharma startup and we’ve built a site where they can log all of their experimental data. There’s plenty of data stored across a set of lists and libraries we built to match their scientific processes. The main storage location is a SharePoint Document Library made up of hundreds of Document Sets.
If I tried to connect to the SharePoint Document Library using the “SharePoint Online List” connection, I end up with this as the query:
let
Source = SharePoint.Tables("https://tenant.sharepoint.com/sites/siteName", [ApiVersion = 15]),
#"57cb07a8-e7aa-4401-a778-699941bfe12b" = Source{[Id="57cb07a8-e7aa-4401-a778-699941bfe12b"]}[Items]
in
#"57cb07a8-e7aa-4401-a778-699941bfe12b"
which gave me the following error (DataSource.Error):
Trolling the Power BI community forums, I came across the suggestion to switch from [ApiVersion = 15] to [ApiVersion = 14]. When I made that switch, the error changed, but still no joy, as I got this error (Expression.Error):
I figured maybe since I was working with a Document Library instead of a list that was part of the problem. (It didn’t really make sense to me that it would, but…) I figured I’d try an OData call to the REST endpoint instead. When I connected to the Document Library as an OData source without specifying anything for the $select, the query ended up looking like this:
let
Source = OData.Feed("https://tenant.sharepoint.com/sites/siteName/_api/web/lists/getbytitle('List Name')/items")
in
Source
…and I got the same error (DataSource.Error) as above.
The column in question here is a multi-select choice column and it became clear that Power BI doesn’t seem to like multi-select columns. I verified this in a conversation with my buddy John White (@diverdown1964). John is a fellow MVP and one of the best BI experts out there. I figured if he didn’t know how this worked, no one would. (Follow John’s Blog ‘The White Pages‘ for all sorts of BI goodness – and more.)
As John and I were talking, it occurred to me that I could just request the columns I really needed in the REST call. Not only might it solve the problem, it would certainly be more efficient. I’ve learned to only ask for the columns I need when I’m making Web Services calls in general.
When I added the $select clause and just asked for the Title column:
let
Source = OData.Feed("https://tenant.sharepoint.com/sites/siteName/_api/web/lists/getbytitle('List Name')/items?$select=Title")
in
Source
Joy! I saw the data in Power BI just fine.
So I think the moral of the story is to build a REST call to only retrieve the data you need, excluding any multi-select columns right up front. As I mentioned, the problem column was a multi-select choice, so there was no option there. I simply couldn’t use that column in my analysis. Luckily that column didn’t really matter to us – yet.
I had better luck with lookup columns – as long as they weren’t multi-select. By using $expand on those columns, I could retrieve the values from the associated lookup list.
Finally, since we only get 100 items back from a REST call by default, I added $top=5000 so that I could retrieve the maximum allowable number of items per request.
Unfortunately, there are some drawbacks to using Power BI right now, and the team will need to solve these for people to really start using it to its full potential in the SharePoint / Office 365 space. Multi-select columns are pretty common in SharePoint lists, and Power BI needs to handle them more gracefully, if not actually use them for analysis. The error messages I got were certainly misleading, and the suggestions about how to fix them were interesting, but not effective in the end.
And it’s very yellow.
So… Two questions: 1. It looks like we are limited when using SharePoint lists: we get only 5,000 records. (Is that correct?) 2. I am assuming that you must be running the Power BI client? Or is there a way to create a SharePoint page that shows a dashboard consisting of Power BI web parts?
@Marcel:
As far as I can tell in this instance, the answer to 1) is yes. I’m not sure if we can go over 5000 items by using the SharePoint List connection method. The 5000 item limit continues to seem absurd to me.
Yes, I’m using the Power BI desktop client. The only way to put Power BI generated analysis into SharePoint pages right now is by using some embedded HTML. Unfortunate, thus requires us to publish our Power BI file – along with our sensitive data – to the Power BI service. This lack of security is a non-starter for many organizations.
M.
Marc thanks for the article! I got stuck with this exact issue the one time I tried and I hadn’t made time to look for a solution. One question, have you found out how data refresh is handled? Ideally, if I query a list for data to build a dashboard/report I’d like the data to be live. When using OData with Excel Services there’s no refresh on a web part and you’re forced to open in Excel to see fresh data which didn’t meet my needs. Thanks again!
@Noah:
I’m really just getting started, so I have similar questions. It seems as though refreshing is an action I have to take if I want to update the data – at least in the Power BI desktop. I agree that with the potential for stale data, this could be a more dangerous tool than a useful one. Decisions have to be made with current, valid data, or we’re still in the dark ages.
M.
Hey guys – refreshing should be no issue. If the source is SPO, it’ll just work. The Enterprise gateway supports OData, so if it’s on-prem, you’ll just need that installed for scheduled refresh.
@John:
What’s the story on Office 365? I would think you wouldn’t need any sort of gateway if you want to keep everything there?
M.
Correct…. no gateway needed for cloud sources – it’ll “just work” once you provide credentials for refresh.
But the best refresh rate we can currently get on SPO lists is hourly and you need a Power Bi pro license right? Not having a live connection for SPO Lists is keeping us from moving a number of reports to Power Bi at this time.
Any while I’m here:
+ No native SharePoint Power Bi web part in SPO
+ No native Rich Text/HTML field type in Power bi
+ No apparent way to send parameters to the report via the URL
More info on Enterprise Gateway here – https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise/
Marc, the new version of Power BI (May, I think) has an option for SharePoint Folder. This pulls in every doc library for a particular site but you can filter on Folder Path to get to a specific one. Most of the metadata has to be expanded from the initial Attributes column. Still nothing for multi-value columns that I can see.
Useful post I I have a requirement where I want to check the ‘CreatedBy’ or Name (user) columns in a Office 365 list ( Survey) ,against the members of a number of emailed security groups .. Hence, I have loaded up in PowerBI Desktop and used SharePoint Online List and also compared against a (Basic) OData: getbytitle()/items .. The results look similar. I don’t seem to be able to see the CreatedBy field but I does pick up a Name field as a Record so will need to cross reference the UPN or DisplayName in each case.. You done anything similaar?
@westerdaled:
The CreatedBy column’s InternalName is Author, and you should be able to retrieve it.
M.