Forum Discussion
SharePoint Online List with Multi Value Person Column
I am trying to turn a SharePoint list into a Power BI chart. On the x axis I want to display a multivalued Person or Group column. If the item has multiple entries in this column, I want them to display together on the x axis, using some sort of delimiter. Before I add the user information table, I see the ids associated with people on the x axis as they should appear. When I created this data source I edited the column to 'Extract Values...' so I would see results other than the word 'list.' I then connected to the user information table, created a relationship between the 2 sources, and now use the Name column from the user information table for the x asix. If only one name is used on the list, the name displays properly on my chart. However, all the items that have 2 or more values are not displaying on the chart. Instead, there is a new (Blank) column on the chart that tallies all the instances of items that have multiple names.
Is it possible to refine this so it will show the multiple values as the names? I found if I recreate the initial data source and edit the column to 'Expand to New Rows' the names are broken apart as their own rows and display on the chart individually, but this then gives a false representation of the number of items being display in the chart.
Hopefully I am explaining myself clearly here. I'd appreciate any guidance or suggestions. Also, if I need to clarify anything please let me know.
Thanks in advance!
I was able to do this using this article as a guide:
Using Power BI to Report on Person Fields in SharePoint
Rather than linking to the User Information List, I went with the option to extract the full name. This gave me just what I needed.
3 Replies
HI weir311,
You can add a calculated column with CONCATENATEX function to merge fields values based on group, then you can use this column on legend fields and it will summary correspond records as same legend.
If you are confused on coding formula, please share some sample data for test.
Regards,
Xiaoxin Sheng
Hi Anonymous , thanks for the reply! So here is a snapshot of some of the data I am using:
There are 5 SharePoint list items above by ID. People can be added in any number to an item. When Power BI grabs the data it made the 2 'Individual on Hold' columns above. What I did above is show the first 'Individual..' column as extracted values, the second I selected 'Expand to New Rows'.
I then added the user information table to relate those user IDs as names. When I display this as a chart using the 'Individual on Hold_xId' column for the x axis, I see 2 columns - first column lists the name as (blank) and consists of the first 2 items, 4 rows above, where 2 people are assigned, and the second column consists of the last 3 items and refers to user 148 by his name.
Can you provide more direction on how I can use concatenatex here? Hopefully I provided enough detail here.
I was able to do this using this article as a guide:
Using Power BI to Report on Person Fields in SharePoint
Rather than linking to the User Information List, I went with the option to extract the full name. This gave me just what I needed.
