Using Power BI to Report on Person Fields in SharePoint

This post is the second in a series exploring Power BI and complex data types in SharePoint. The first post explores working with multi-value columns. In this one, we’ll explore some of the nuances of working with person fields

Person fields in SharePoint are just a special case of the lookup field, and the Power BI SharePoint list connector is aware of them. As such, it provides helpers to make it relatively easy to get the person’s name. However, more information is also available. We’ll examine three approaches to extracting this information. It is worth noting that all SharePoint lists contain person fields for “Created By” and “Modified By”, and they are always available.

The List

Consider the following list that contains a multi-value choice field named Amenities:

The view displays the person’s name, although the column is a complex data type. There is more information than just the person’s name available behind it but this is unavailable to the SharePoint view. Power BI can however access this information in reports. Report requirements will ultimately dictate the best approach to extracting this information, but the good news is that there are several to choose from. In all cases the data first needs to be brought into Power BI Desktop.

Loading the Data

We first launch Power BI Desktop, select “Get Data” and then choose SharePoint Online list (if connecting to SharePoint Online) or SharePoint List (if using SharePoint Server). We are then prompted for the URL of the SharePoint Site. The dialog is titled SharePoint lists, but the value is actually the URL of the site, NOT the list itself. Once this is entered we are prompted for credentials if we haven’t connected to this site before. After entering credentials, we can select the list that we want to report on. In our case, it’s named “Listings”. We select it, and then click on the Edit button.

Once the data loads in, one of the first things that you’ll notice is that there are a lot of columns to choose from, and it’s a good idea to remove the data that you don’t need. In this case, we can remove the ContentTypeId column and everything to the right of it, with two important exceptions We want to keep the “FieldValuesAsText” and “Agent” columns (we’ll come back to that shortly). Remember, for our purposes here, we want to report on the person column, “Agent”. The simplest way to represent this data is with the person’s full name, as it is displayed in the SharePoint view. As noted above, it is also possible to use this data in a more sliceable, or structured way. Let’s start with the simplest.

Extracting the Full Name

One thing that you will notice right away is that he more simple column types like “Title” show their value directly in the Query editor. In our case, there are two fields related to Agent, the “AgentId” and “Agent” columns. The Agent ID column displays a number, and the “Agent” column displays a record data type. We will explore these columns, but if all we need is the user’s full name, we can use the highly useful “FieldValuesAsText” Column.

We scroll right and select the expander icon for the “FieldValuesAsText” column, then deselect all available fields except the Agent column.

We then select OK, and rename the column to “Agent Name”. The Full name of the Person is retrieved and used for the column. At this point, it’s ready to use in a report.

Linking to the User Information List

In many cases, the user name of the person may not be enough. As mentioned above, the Person Field is really just a lookup column that is automatically looking up data from a specific list. That list is the User Information list which is a hidden list that exists in the root site of every SharePoint site collection. This list gets populated automatically when the site collection is accessed. When Power BI loads a person column, it automatically creates a ColumnNameId column as well containing the ID value of the person field from this list. In our example, this is the “AgentId” column.

To leverage the data in this list it must first be loaded into the model. Following the same steps taken for loading the Listing data above, we select the “User Information List” which does get exposed to the Power BI Query editor. Once loaded, we remove all of the unnecessary columns from the query, being sure that we leave the ID column.

When ready, we select the “Close and Apply” button from the Query Editor Ribbon. At this point, we have two tables in the model, Listings and User Information List. We then select the relationship editor tab. The “AgentId” column in the Listings table is related to the “id” column un the User Information list table, and we establish this relationship by dragging one onto the other. Once established, we double click on the relationship line to set the value of “Cross filter direction” to “Both”.

We can now return to the design pane, add a table visual, and add columns from both tables. In such a way, we can show the agent’s name, email, phone, etc.

Expanding the Person Column

Although linking to the User Information List is powerful, and easier, and arguably better way to do the same thing is to use the automatically generated person column. This column is named the same as the original person column and contains a series of “Record” type values. The records in question are the corresponding records from the User Information List.

To access the data in this column, we click on the column expander and then select all of the columns that we will work with. Values from the related User Information List will be added to the table automatically.

This approach is clearly simpler than manually loading the entire User Information List, and only loads the records that are related. It will however likely result in a large amount of repeated data that the two table approach avoids. It is possible to achieve a two table solution with the person field using the technique outlined in my earlier article on working with multi-value fields, but the resultant table will still only contain related records. If it is necessary to show people regardless of whether or not there is a related record, then the manual approach is the only way.

Which approach is ultimately used will depend on the requirements of the report, but it is possible to reach deep into the person object in a SharePoint person field.

Using Power BI to Report on Multi-Value SharePoint Fields

Power BI has direct support for reporting on SharePoint lists and documents whether SharePoint is on-premises or in the cloud. Getting at your data is relatively straightforward if you know the URL, but as I’ve written about before, SharePoint data can be idiosyncratic. Text and number fields work right away, but more complex data types require a little more thought, and this is certainly true of multi value fields.

Multi value fields are really SharePoint’s way of approximating the behavior of a one-to-many relationship of a relational database. SharePoint can store list-based data, and even though it has the concept of a lookup field, it is not a relational database. Chances are however that if we’re reporting on this type of field, we want it to behave as though it were. Fortunately, Power BI gives us several options for doing this.

The List

Consider the following list that contains a multi-value choice field named Amenities:

In the view, the different values are flattened, and displayed as a single value using a comma to separate them. How do we go about reporting on this data in Power BI? There’s no right answer, as it depends on the report requirements, but there are several options. In all cases the data first needs to be brought into Power BI Desktop.

Loading the Data

We first launch Power BI Desktop, select “Get Data” and then choose SharePoint Online list (if connecting to SharePoint Online) or SharePoint List (if using SharePoint Server). We are then prompted for the URL of the SharePoint Site. The dialog is titled SharePoint lists, but the value is actually the URL of the site, NOT the list itself. Once this is entered we are prompted for credentials if we haven’t connected to this site before. After entering credentials, we can select the list that we want to report on. In our case, it’s named “Listings”. We select it, and then click on the Edit button.

Once the data loads in, one of the first things that you’ll notice is that there are a lot of columns to choose from, and it’s a good idea to remove the data that you don’t need. In this case, we can remove the ContentTypeId column and everything to the right of it, with one important exception We want to keep the FieldValuesAsText column (we’ll come back to that shortly). Remember, for our purposes here, we want to report on the multi-value column, Amenities. The simplest way to represent this data is as a string of comma separated values, which is the same way it is shown in a SharePoint view. It is also possible to use this data in a more sliceable, or structured way. Let’s start with the simplest.

Extracting a Simple Text Field

If our report is to show all the amenities associated with a listing, then a simple text string will do the job. Examining the data in the Query Editor, you will notice that there are no values displayed for Amenity, the way that there is for simpler field types like “Sold” or “Asking Price”. Instead, each cell contains a “List Value”. Each of these Lists contains the Amenities values for each record and clicking on one will extract those values for that one item. However, of we want to extract the values for all items, so we need to expand the column by clicking on the expand icon in the column header.

We are then given two options, “Expand to New Rows” or “Extract Value”. To extract the values into a single value, we select “Extract Values”

We are then given the opportunity to choose a delimiter, and after that the values in the column are replace with simple text values.

Another way to accomplish this same thing is to use the FieldValuesAsText column (referenced above). The fields in this column contain a single record containing the textual values for all the complex data types in a SharePoint list. If you are doing reporting with SharePoint data in Power BI, FieldValuesAsText is your friend. To use it, we just click the expand icon in its column header, deselect all of the columns that we don’t need (which in our case is everything except Amenities) and click OK. We are returned the contents of the list in a comma separated string for each item.

At this point, we can report on the data.

We click on Close and Apply in the ribbon to return to the report canvas. We add a table visual, and add some of the fields like address, street number, and Amenities to it. You’ll see that the table looks much like it would in a SharePoint list. Next, we add a slicer to the report page, and use one of the other columns, like City. Clicking on a city will filter the table by that city as expected. Now, if we want to filter by Amenity, we can add the Amenity column to the slicer, but it won’t behave the way we want it to. Each combination of amenity values will be represented as a single value. We are unable to slice on a single value like “Garage”.

Flattened multi value field as slicer

To use discrete values for our multi-value field, we need to do a little more work in the Get Data (Power Query) interface.

Extracting Discrete Values from a Multi-Value Field

We follow the initial steps from the “Simple Text Value” section above, but when the expand icon is selected, we select “Expand to new rows” and NOT “Extract values”. When this is done, each row will be copied to accommodate all of the individual Amenity values. If there were 3 Amenity values, we wind up with three rows of identical values, but with each of the values for Amenity.

Expanding to new rows

We can now go to the design surface and add a table, and a slicer that uses the multi value column, and now we can filter on discrete values. This may be sufficient in many cases. However, if we create a measure that based on an aggregate value for the table (like COUNTROWS or SUM), the value will not be correct if there are no slicer values. It works with all of the duplicated rows.

Incorrect number of listings

Again, this approach may work in many situations, but for ultimate flexibility, we need a true one-to-many relationship. Luckily, Power Query gives us a few simple tools to do this.

Creating a One-to-Many Relationship

Again, we follow the initial steps from the “Simple Text Value” section above, but do not click on the expand icon in the Amenities column. A One-to-many relationship requires at least two tables, so now is the point where we need to add a second one. We do so by copying the first. We right click on the query in the Queries pane and select “Duplicate”.

This creates a second query identical to our first, which will load into another table. We can then give the new query a better name, like “Amenities” in our case. Next, we select the Id, and the Amenities columns, right click and select “Remove Other Columns”, and we are left with only the ID and Amenities columns. Now we select the expand icon and select “Expand to New Rows”.

We now go back to the original query and remove the Amenities column. The result is two tables, Listings and Amenities, with Listings containing the bulk of the information. We then click on Close and Apply in the ribbon to load the data into the model, and the relationships icon to take is to the relationship builder. We need to establish a relationship between the two tables, and we do so by clicking on the Id field in one table and dragging it to the Id field of the other. Finally, double click on the relationship line between the tables, and be sure to select “Both” for Cross Filter Direction – that way Amenity values can filter List values. If “Both” is selected, the directional arrow will point two ways.

Now, the same visuals from above can be added using the Amenity column from the second table as a slicer, and everything should work as expected. Listings will be filtered by the value, if no amenities are selected, the correct number of listings will be shown In the data card, and no duplication will appear in the table.

SharePoint multi-value columns are an attempt to replicate the capability of a relational database. With a few simple steps, Power BI can take SharePoint data, normalize it, and analyze it as if it were a true relational database.

Using Power BI for Facebook Location Tracking

Three years ago I wrote an article about how to map your Facebook check in in Excel using Power Query and Power View. Given that Power View has gone the way of the Dodo for all intents and purposes, I have been meaning to rewrite it using Power BI to do Facebook location tracking. During my recent Power BI tutorial at the European SharePoint Conference I was also informed that the Power Query behind it no longer works. Something had to be done!

The Facebook Graph API that Power Query uses changed significantly about a year ago, making location data much more difficult to discover. However, it is still possible to do, and below, I outline the process for working with Facebook data in Power BI. The below example is to map and analyze Facebook check ins, but the principles apply to all sorts of Facebook content.

Building the Query

To begin with, you’ll need to be running Power BI Desktop. You can do everything necessary with Desktop, but if you want to deploy to the service for automatic updates or for sharing, you’ll also need a Power BI account at app.powerbi.com.

To begin with, launch Power BI Desktop, select “Get Data” and scroll down to Facebook. Alternatively, you can select the “Online Services” node, and select Facebook from there. Click the Connect button, and if prompted, enter your credentials. Once that’s done you are prompted to enter an object id, and a connection.

Connecting Power BI to Facebook data

There was a time when this could be used to gather data from any Facebook user, but with changes to the Facebook API, the Me object, or a Facebook page are the only objects that will work. The connection parameter allows you to select the data stream that will be returned. In this case, you will need the “Posts” connection. Once you click OK, you will be presented with a preview of the results. You will want to transform this data, so the next step is to select Edit.

At this point, you will notice that there are columns for message, story, time, Facebook ID, and object link, which contains further data about comments and likes. However, what you won’t find is any locational data, or even an indication as to the type of Facebook post. This will come as a surprise to people that used earlier versions of the Facebook connector. However, the data is still available.

If you look at the data source, you’ll notice that the “Me” and the “Posts” parameters entered above was used to form a URL. This URL queries the Facebook Graph, and the default result of that query is what you receive.

Power BI connecting to the Facebook Graph

Checking the Facebook Graph documentation, you will find many different options for the Posts query. Most important is the ability to specify what data it returns, and indeed, there is a field named “place” that will return any location data for a post. Appending “?fields=place” to the query will cause the place field to be included to the result. However, if you do this, you will notice that most of the default fields are no longer included. You therefore need to include all fields wanted explicitly. Additional fields are specified by separating them with a comma. In our case, we will also return the fields for picture, and post URL. Our query is therefore:

Source = Facebook.Graph("https://graph.facebook.com/v2.8/Me/posts?fields=place,message,story,created_time,id,permalink_url,picture")

This query will return the posts and any available location data. If you want to only get the posts with location data, you can also add a filter specification to the query by appending “&with=location” to it. The full query therefore becomes:

Source = Facebook.Graph("https://graph.facebook.com/v2.8/Me/posts?fields=place,message,story,created_time,id,permalink_url,picture&with=location")

Adding query parameters to the Facebook Graph in Power BI

At this point, we have location data, but it’s in the form of a record, and you need to expand it. You can do so by clicking the expander icon at the top right of the column header. The only field that you want to retain is location, so first deselect the “Use original name…” checkbox and the “(Select All Columns)” checkbox, then select location, and click “OK”.

Expanding the Facebook Place field in Power Query

This returns location, also in the form of a column. Repeat the same steps to expand this column, but this time select all of them.

Expanding the Facebook location field in Power Query

After selecting OK, your data has 4 new columns for the geographic information.

At this point, you need to flag the data types of your columns. Set the latitude and longitude to the decimal type, the created time column to Date/Time/Timezone and delete the object id column. If like and comment data is wanted, it can be added later in a separate query. Once the correct data types are set, select Close and Apply to load the data into the data model.

Building the Report

At this point you have all the data you need. If you want to use the permalink url and the picture columns to display links and pictures, you’ll need to flag the data category appropriately. You’ll also need to do this for your geographic columns. This is done by selecting the field in the fields selector, then opening the “Modeling” tab, and selecting the appropriate value for “Data Category”. Once done, the url field will render as a link or icon, the picture field will render as an image, and the geo fields will work on a map.

Setting Data Category properties in the Power BI data model (PowerPivot)

The precise values to set these fields to are as follows:

Field Category
city City
country Country/Region
latitude Latitude
longitude Longitude
permalink_url Web URL
picture Image URL

Once set, it is possible to build a report displaying check ins on a map, as a function of time, along with slicers and detail. By adding in comment and like data to the model as well, it is possible to create a report like the one below. To see how it was built, download this Power BI Desktop template file, open it in Power BI desktop, and enter your credentials. The report will populate with your check in data, and you can then see precisely how this report was built. If you just want to see all my Facebook check in data, you can slice and dice the report below. If everything is working properly, it should be updated daily. You can also see it fullscreen here – http://link.tygraph.com/FBJPW 

The important thing to note is that although the Power BI connector may not retrieve the data that you need for analysis by default, the full power of the Facebook graph is there for you to take advantage of. This is particularly valuable for doing analysis of Facebook pages.

 

Get Power BI Desktop from the Microsoft Store

If you’re a Power BI Desktop user, you are likely familiar with the monthly update cadence. Once per month, a new Power BI Desktop is released, and normally it contains at least one new feature that you WILL want to use. In case you miss it, a little indicator will light up in the bottom right hand corner that will prompt you to update. Of course, you normally don’t notice this until you’re in the middle of designing something, and it’s never a good time.

Then you do click on the prompt, you’re take to a web page from which to download PBI Desktop. Doing so can take a few minutes (it’s relatively large), and when it starts, more often than not, you’ve forgotten to exit the current PBI Desktop. Once you do, you can run the installer and then you’re up to date. For 30 days. While I think that most of us welcome the new features, this update process itself is cumbersome. Thankfully, the availability of Power BI Desktop in the Microsoft store removes this pain.

Power BI Desktop – Microsoft Store Edition

Getting Power BI Desktop from the Windows store allows you to have it automatically updated in the background whenever new versions are available, just like any other app in the store. To be sure, it is not a separate “Microsoft Store Edition” of the product, but precisely the same product in a Windows Centennial (presumably) wrapper.

Installing Power BI Desktop from the Store

Before installing the store version of Power BI Desktop, it’s a good idea to uninstall the older version. This is not required, and the two can operate side by side, but this will most likely be confusing. The non-store version is uninstalled by opening “Programs and Features” in Control Panel and uninstalling from there. As an aside, the store version will not appear in this list – like other apps, it will only appear in the start menu, and if necessary can be uninstalled by right-clicking on the tile.

To install PBI Desktop, open the store and search for Power BI. At the moment, PBI Desktop does not appear in the search dropdown, but the Power BI app does. The app is not what you’re looking for here. Execute the search and you should see two results, Microsoft Power BI Desktop, and Microsoft Power BI. The icons for the two are very similar (but not identical!). Be sure that you select he one with “Desktop”.

Installing Power BI Desktop from the Microsoft Store

The power BI app is the Windows 10 app for Power BI and is designed for a mobile experience. It is like the versions found on iOS and Android. Selecting the Desktop tile should download and install Power BI Desktop.

Copy Your Saved Settings

One of the downsides of this new isolated model is that all the stored credentials, cached data and saved settings from your prior versions of Power BI desktop do not come forward into the store version. However, the good news is that you can bring them forward manually. To do so, navigate to your AppData folder for Power BI, likely at:

C:\Users\[YourUserName]\AppData\Local\Microsoft\Power BI Desktop

Copy everything in this folder (including subfolders) and copy it into:

C:\Users\[YourUserName]\AppData\Local\Packages\Microsoft.MicrosoftPowerBIDesktop_8wekyb3d8bbwe\LocalCache\Local\Microsoft\Power BI Desktop Store App\

The latter is simply the isolated store app version of the former. This content can be relatively large, so it’s a good idea to delete it from the source once finished. Uninstalling the original application does not clean up this content – in fact in my case, as advised above, I uninstalled it prior to the installation of the store app itself.

Once you’re up and running with the store version, you should never need to worry about manually updating Power BI Desktop again.

OneDrive is Ready – It’s Seriously Time to Ditch the X: Drive

If you are in information worker of any sort, and have been at it for any more than a couple of years, you’ve experienced it – the X: drive. Or the S: drive, P, R, whatever the letter. It’s the drive letter that is mapped to a network based file share that contains most of the company’s documents.

My first experience with IT was in 1989, setting up and managing a Novell Netware 3 network for a University department. Logging into the network (through a DOS prompt) would automatically add a drive to your machine with all of the resources that you needed, the storage you could ever want (measured in MB). It was “magical”.

This basic model exists to this day. We’ve tried to move away from it, we’ve tried very hard. We’ve had large monolithic document management systems imposed from above like FileNet and Documentum. These solutions gained success in specific areas, as they were mandated from above. SharePoint itself came along and democratized document management to a much broader degree, but the pesky X: drive still persists. Why?

One word. Usability.

End users want to be able to open up File Explorer, navigate to their drive, browse their folder structure and work with their documents. The drive mapping metaphor has succeeded so well because it fits this scenario perfectly, and its familiarity. Ever since personal computing began, we’ve accessed file storage using drive letters.

Users use formal document management systems reluctantly. This is often due to overzealous metadata requirements (just fill out this 20 field form to store your document), burdensome procedures, or performance issues. However given the choice, they retreat to the familiarity of their file systems, and their X: drives more often than not.

Consultants and vendors preaching a new way of doing things are in the end shouting against thunder. We can’t expect users to adapt to new systems quickly – what we need, at least transitionally is for the systems to adapt to the users. This is where OneDrive comes in.

One of the most compelling features of SharePoint 2013 in my opinion was OneDrive for Business. The reason that I felt that was that for the first time, SharePoint document management was tightly integrated into File Explorer. There had been previous attempts at synchronizing (SharePoint Workspace), but that required separate client software and required a lot of manual dragging and dropping.

The implementation of OneDrive for Business was initially hobbled by restrictions and limits, and was rather confusing, limiting adoption. However, through the combination of the current OneDrive sync client, with the Files On Demand feature available in the Windows Fall Creators update, OneDrive is truly ready for widespread adoption. Over the past few years, OneDrive has become both reliable and fast, and Files on Demand combined with in-context sharing put it over the top.

OneDrive Files on Demand

Files On Demand in File Explorer

Screenshot taken while flying on airplane mode from a laptop containing a single 256 GB drive.

Files on Demand allows you to easily control what files are synchronized to your local device, while still being able to see all your file assets, directly from File Explorer.

In the figure above, the cloud icon indicates that that folder is not currently stored locally. That’s a good thing, because opening up the folder properties reveals that it is over 1 TB in size, and the drive on the laptop (that I’m currently writing this with) is only 256 GB. Moreover, that screen shot was taken while flying, and totally disconnected. I could still see things that were not on my local device.

If connected, cloud files can be interacted with the same way that local files can, by any application. Opening them just requires a little more time as the file is downloaded in the background. If you will be offline, bringing a file local is a simple matter of right clicking on that file, or folder, and selecting “Always keep on this device”.

Files on Demand is currently available to Windows Insiders, and will be generally available with the Windows Fall Creators Update on October 17, 2017.

In Context Sharing

OneDrive sharing from File Explorer

The new OneDrive in context sharing experience

Up until very recently, sharing a drive from Explorer was a rather frustrating experience. You could identify your file, right click on it, click on share, and then a browser window would pop open, and if you were lucky, you would be presented with a view of the OneDrive web user interface. This experience was jarring, and required multiple steps.

Over the past summer, OneDrive rolled out updates that change this behaviour significantly. Now clicking on share brings up the dialog above and sharing is done completely from there. No context switching, and no authentication failures.

Time to move

These two new features, combined with the performance and reliability improvements over the last few years puts OneDrive over the top. Finally, all of the usability issues have been addressed. End users can live completely in File Explorer should they wish to do so, and be oblivious to the workings of OneDrive and/or SharePoint. However, at the same time, they will gain significant benefits compared to the shared file system.

However, OneDrive provides much more than simply an alternate storage location for your files. Once content is stored in OneDrive, a whole host of options are opened. The organization benefits because all this content is immediately made discoverable through technologies like Delve and Search. File access can be tracked, helping users understand what content others find valuable. There are, however, many immediate benefits that occur directly to the users themselves. I wanted to call out three of them, but there are many more.

OneDrive File Viewing

OneDrive comes with a long list of file viewers. These viewers allow the contents of a file to be viewed without opening the underlying application, which tends to be significantly faster than opening the host application. In fact, the application itself does not need to be installed. This is valuable in itself, but when combined with Files On Demand a file can be viewed whether or not it is even stored locally. A large Adobe Illustrator file can be viewed locally without it even being present on disk. Files On Demand is also available on Mac, and in the OneDrive client, and therefore, this very same file can be viewed on iPhone, Android, iPad, anywhere the OneDrive application is available. This, to my mind, is a game changer.

Sharing

Sharing with OneDrive is not new, but sharing directly from the explorer window is. That sharing experience is also now being consolidated across devices, and embedded into Microsoft Office applications. In addition, OneDrive files can be shared externally with an anonymous link, or securely with others that have a Microsoft account (personal or organizational), but what will be shortly available is the ability to securely share files externally with people that have any type of account.

Files Restore

Announced at Ignite 2017, FilesRestore provides end users with the ability to easily track all versions of their files for the past 30 days, and to instantly restore them to a point of time anywhere in that window. Administrators have long had this capability through a set of operations, but FilesRestore puts this capability into the hands of the end user with a simple to use user interface. Users can rest assured that their files are safe not only from disaster, but from their own mistakes, malware, ransomware or anything. Files stored in OneDrive are safe.

These are but three compelling benefits that users can enjoy by moving content to OneDrive. There are many more. Foe a good overview, and to hear all of the OneDrive announcements from Ignite 2017, be sure to check out “OneDrive – Past, Present and Future“.

It’s time to ditch the shared X: drive once and for all.