Forum Discussion
Extract multiple names from a SharePoint People Picker field
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 ...
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.
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.
- In the Power Query editor make sure your multi-people picker column is displayed, the value for every row will be "Table"
- Click on the "Add Column" Tab
- Click "Custom Column"
- Name the new column
- Under "Available columns", select the multi-people picker column and click "<< Insert"
- 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]
- Other available fields are:
- id, value, title, email, sip, picture, jobTitle, department
- Click Ok, the new column will now be displayed with "List" as the value for each record
- To expand the list, click on the button in the new column's header and select "Extract values..."
- Select a delimiter, e.g. "semicolon" and click "Ok"
- Now the people's names will appear in the new custom column.
- Click the "Home" tab and then "Close and load" to see the new column appear in the Excel worksheet or Power BI.