Forum Discussion
PowerQuery transformation for Sharepoint List multiple People field
Dear all,
It is quite simple. I would like to extract data from sharepoint list "multiple selection people" field. I extracted data into "Title" and "Email" fields and got an error. Not sure how to get from here to simple string representation of array of titles/emails with specified delimiter.
Thanks in advance,
Filip
2 Replies
Hi， Flip89
It's a known issue when using SharePoint multi-select lookup fields in Power BI .
https://ideas.powerbi.com/ideas/idea/?ideaid=d1ecac5a-a018-4865-a581-9e48953c12ca
It is suggested to open a support ticket to get dedicated support .
https://powerbi.microsoft.com/en-us/support/
Best Regards,
Community Support Team _ Eason- 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.
