Getting Up and Running with Power BI and SharePoint Lists

Power BI

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:

  Source = SharePoint.Tables("", [ApiVersion = 15]),
  #"57cb07a8-e7aa-4401-a778-699941bfe12b" = Source{[Id="57cb07a8-e7aa-4401-a778-699941bfe12b"]}[Items]

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:

  Source = OData.Feed("'List Name')/items")

…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:

  Source = OData.Feed("'List Name')/items?$select=Title")

Joy! I saw the data in Power BI just fine.

Just the Title

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.

PerformancePoint Moving into the SharePoint World

My colleague Mauro Cardarelli posted about this the other day:

By now, you have probably heard about Microsoft’s decision on the future of PerformancePoint Server (Microsoft Business Intelligence Announcement Q&A).  Kudos to friend Chris Webb for breaking the news… at least to me!

For PerformancePoint jocks, this is probably bad news, but as a SharePoint junkie, it sounds great.  Having even more Business Intelligence (BI) capabilities available in the SharePoint platform can only be good news.

Mauro’s concerns about his closet space aside, he’s right about the dearth of good talent working with SharePoint today.  But will this create a bigger mess or a bigger set of opportunities?  I think the latter.  This will allow organizations that already are using SharePoint to extend that use even further into the BI space.  Many organizations are already storing some of the data that can feed their BI in SharePoint, so the layering on of more tools simply gives them more options.  Yes, it will make the SharePoint platform bigger and potentially more confusing, and no, there aren’t a lot of folks out there who even understand all of what is included currently (myself included — it’s just too big!), but I expect that we’ll see more specialization inside the SharePoint space to make up for it.

Mauro is certainly right (as usual) that it’s some of the “softer” stuff (strong governance, solid information architecture, tight security models) that make or break SharePoint success.  Those things will become even more important as SharePoint’s capabilities expand.  As I always tell my clients, the best SharePoint developer is one who knows when *not* to code, but to take a step back and talk about the underlying business goals, processes, and incentive changes which are going to be required for the technology implementation even to make sense.