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.
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.
Thanks much for sharing such an essential worthful article! I really enjoyed reading this article as your explanations are clear and insightful. You have given us step by step instructions with some good images and link to your previous post related to this, thank you very much.
Thank you! This is very helpful.
Very clear and concise – exactly what I needed.
Real help. Sorted my issue
Thanks so much, this really helped me get those pesky user names
Great article, this is very helpful!
How do I get the “hidden” user information list to show up? Where do I get it from? Do i need admin access to my own site?
First thanks the sharing.
I am having problem when expand “FieldValuesAsText”, whatever any change in query, it take couple hours to load it and refresh it, it seems file size up to 10+ times when load from sharepoint, once all the refreshed completed, Power BI file back to save small size. it drive me crazy to wait and wait.
Could somebody help?
How does Sharepoint + PowerBI handle the person field if a person has left the organization? Does it still retain data like Job Title, Manager, etc from the time it was captured on the list or do those values change behind the scenes when something from their O365 profile changes?
Great write up John. Super helpful
Very useful and time saving information. Thanks a lot!
thank you.. this is very helpful
Great content; terrible format to display it. Would work better if you chunked this up into a bulleted list with concise directions. Also, make the images bigger or place a link to their full size.
Same commenter as above ^^ Take that back. This is pretty hard to follow if you’re coming here after you’ve loaded the data & you’re in the Query Editor. Not sure if this is because this is outdated and the Power BI Desktop UI is different, but the first option is a no go.
Awesome. Thank you so much. This was well documented. I chose to expand the person column and experienced no problems.
Attention, this seems not tow ork with current/new/Beta 2.0 sharepoint online connector! The fields are just not there!
Excellent. This suggestion helped me a lot, this feature is not document in any main stream documents. Thank you so mych
Excellent. This suggestion helped me a lot, this feature is not document in any main stream documents. Thank you so much
Great. That was an icebreaker
This is out of date, there is no Edit when getting source from Sharepoint list, how to fix this?