Extract multiple names from a SharePoint People Picker field

[Anonymous]

Starting a new topic since I think my question has changed.

I had a Power BI report which pulled from a SharePoint list. This list had (among other fields) three People Picker fields. Originally configured for one name max these worked fine, and was even used as a filter in some visuals. Now I have changed the People Picker fields to allow up to four names, and it's working fine in SharePoint, BUT...

1. It broke refresh in Power BI. If I remove those columns from query the refresh works again, but not sure this is what I want. Is there a way to configure the column query that works with multiple names stacked into one column? And if so...
2. Any thoughts on a DAX way to separate said names. Since there's a max of four and the names are delineated by a semicolon it seems like it might be possible.

Any thoughts?

[MementoVivere]

How to expand a SharePoint multi people-picker field in Power Query (in Excel or Power BI).

This assumes you have a Power Query data connection via: Get Data -> Online Services -> SharePoint Online List that has a multi-people picker column in the List.

1. In the Power Query editor make sure your multi-people picker column is displayed, the value for every row will be "Table"
2. Click on the "Add Column" Tab
3. Click "Custom Column"
4. Name the new column
5. Under "Available columns", select the multi-people picker column and click "<< Insert"
6. To expand the email attribute for each user, add [email] after the column name in the "Custom column formula" box, like this: [People Picker][email]
7. Other available fields are:
   1. id, value, title, email, sip, picture, jobTitle, department
8. Click Ok, the new column will now be displayed with "List" as the value for each record
9. To expand the list, click on the button in the new column's header and select "Extract values..."
10. Select a delimiter, e.g. "semicolon" and click "Ok"
11. Now the people's names will appear in the new custom column.
12. Click the "Home" tab and then "Close and load" to see the new column appear in the Excel worksheet or Power BI.

[Icey]

Hi @Anonymous ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

[Anonymous · Accepted solution]

Hello. Sorry for delayed response. This is solved, but via none of the entries in this thread. I had to go back to my SharePoint list and my Flow and make structural changes to how the names/addresses are handled and that fixed everything.

As the solution isn't really topic-appropriate to this forum I'll exclude it, but if anyone comes across this with a similar issue and needs an answer don't hesitate to contact me.

[PatelYU]

Daryl,

I am just trying to do what you orginally asked in the fact i have 3 people picker fields in my sharepoint list which could have multiple entries. how did you get power bi to extract the names of the people?

[trebgatte]

Hi Daryl,

You have to do this in Power Query M, not DAX. The multivalue fields are now located in FieldValuesAsText, which has to be expanded to get to the individual fields. Once that occurs, there's a number of transformations to perform. I'm working on a blog post on how to do this currently. You can use the Split column transform to separate the values.

Hope this helps!

--Treb

Check out my Power BI blog posts at https://marqueeinsights.com/tag/power-bi/