A Simplified Method of Working with SharePoint Data in Power BI

Although I typically advise against it, there are valid reasons to report on SharePoint list data directly. Power BI Desktop makes this data quite easy to access – you can use the built in connectors for SharePoint or SharePoint Online, or, due to the fact that any SharePoint list is available via OData, you can also use the OData data connector. Microsoft has recently made improvements to both methods, but the SharePoint connectors bring some significant usability advantages. One of these advantages is what I’m calling “summary columns”.

The Problem

Consider the following SharePoint list with different field types:

Connecting to this list with Power BI desktop and editing the query returns all of the list fields regardless of their visibility in the user interface. Assuming that we want to work with the above field values for analysis purposes, we can discard the fields that we don’t need, and reorder the remaining, leaving us with only these fields.

However, you can immediately see that we’ll need to do some work in order to get our data in a usable format.

The title field is simple enough – it’s value is immediately available, no issues there, and no changes are necessary. From the Name field, several columns are returned. Two are the ID of the name in the site collection’s user list. It would be possible to connect to the root of the site collection, retrieve the user list, and establish a relationship between the tables, but clicking the expand icon will allow Power Query to do a lookup for each ID and return the desired attribute, in this case, Name.

The same is true for the lookup field type – the column can be expanded in order to include any attribute of the source item. The field using managed metadata works in a similar fashion; it can be expanded in order to retrieve the text value of the managed metadata item. Link fields work the same way – the can be split into two columns, the link itself and the description. However, the field containing multiple values is a little different. The great news here is that it’s possible – previous versions of Power Query couldn’t work with multi value fields, but now the SharePoint data source supports it.

Multiple value field values are returned as a list. With list items, the expand icon will duplicate the entire record for each value on the list. This may or may not be the desired behaviour, but remember, this is Power BI. Everything can be aggregated.

Before After

The conclusion to be drawn here is that in order to represent SharePoint list data that is using any sort of control more complex than text or number, we need to do some work. However, the good news is that someone (I’m not sure if it’s the Office Team of the Power BI team) has added a feature that makes this whole process much simpler.

The Solution

After connecting to your SharePoint list, edit the query. Instead of diving in and performing all of these manual transforms, select your multi value column(s) if you have any (this will make more sense momentarily). Select any rich text columns as well, and then scroll right to find a column named “FieldValuesAsText”. Select this column, then right click on it and select “Remove Other Columns”.

The FieldValuesAsText column is our magic bullet. It automatically converts most (rich text fields being the exception) of the more complex SharePoint data types to simple text that work well within Power BI. Simple click on its expand button, select the columns that you want to include in your analysis. I find it useful to deselect “Use original column name as prefix” as well. We are left with textual representations of our field data.

You will notice that the multiple value fields here have their values separated by commas. For multiple values, I tend to prefer the “raw” approach, which is why we retained the multiple value column above. We can still expand it and create a separate line for each value, and remove the column created by “FieldValuesAsText”.

Finally, you may have noted that the Rich Text field isn’t automatically converted. In order to extract useful text from it, we still need to use Power Queries transformation functions such as Replace Value, Trim, and Clean.

In a nutshell, if you’ve been frustrated by formatting or data type limitations when using SharePoint data in Power BI, have another look, and check out the FieldValuesAsText column. It will make everything a lot simpler.

Power BI and Shark Attacks – Oh My!

It’s not often that you get to combine interests, but when the Power BI team launched the Publish to Web feature for Power BI reports, I saw just such an opportunity. Quite some time ago, I had played around with the Global Shark Attack File for some Power View demos. One of the problems at the time was that there was no way to keep it automatically refreshed, and there was no real way to publish publicly.

Publish to web removed the second problem, so I doubled down on the first, figuring out a way to automatically download the Excel workbook on a nightly basis from the GSAF site. With that in place, armed with the Personal Data Management Gateway for data refreshes, I set out to do a little data modelling, and report building. The data in the file isn’t in the best shape, so it took a bit of work with both Power Query and DAX to beat it into t shape I wanted it (particularly extracting the species of shark).

However, as seems to be the case with Power BI, most things are possible, and I managed to put together something fairly interesting. I was inspired by a recent article in California Diver magazine, which had been shared around on Facebook. The article claimed that there were zero attacks on SCUBA divers in 2015, which is of course good news. Being an avid diver, that loves the sharks, I’m constantly amazed at the fear these animals generate. Cows kill a lot more people each year than sharks, but I digress.

I was quickly able to see that the figure for 2015 was in fact 1. The attack happened in Brazil on Dec 21, and it was non-fatal. Presumably, the article writer had a Chrstmas deadline. In any event, don’t trust me, check it out for yourself. The report is too wide to embed in this blog post, but I have created a page to host it here:

Global Shark Attacks

It is published with the Power BI Publish to Web feature. Provided that my workstation is up and running, along with connectivity, it will be updated every morning at 6:30 AM Eastern time. If you have any ideas for improvement, please let me know! I am interested to see how well this feature works.

This also seems as good a time as any for one of my favourite diving pictures. The shot below was taken of yours truly by my son while diving with a group of about a dozen Bull Sharks.

IMG_6576