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("http://ift.tt/23GJB1s;, [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("http://ift.tt/1Wp27wM('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("http://ift.tt/1Wp27wM('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.

by Marc D Anderson via Marc D Anderson's Blog