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.
21 Comments
