Skip to main content

How to Get the Email from a SharePoint Person Column in Power Query

· 6 min read
Jagdish Kumawat
Founder @ Dewiride

A SharePoint Person column doesn't arrive in Power Query as a name or an email. In the SharePoint Online list connector's implementation 2.0, it shows as [List] or [Table]: a structured value with the person's id, name, email and more. Expanding it gives you a plain email column. Here's how, for a column that holds one person and for one that holds several.

The examples are two lists read with the SharePoint Online list connector (How to Get Data from a SharePoint List in Power BI in the Browser):

  • Expenses has SubmittedBy, a Person column that holds one person. Power Query shows it as [List].
  • Expense Categories has Managers, a Person column that allows several people. Power Query shows it as [Table]. The Meals category has two managers.

Emails are useful in a semantic model: row-level security matches them with the signed-in user (How to Get the Signed-In User in Power BI with USERPRINCIPALNAME).

A Column with One Person​

Step 1: Expand the List​

Select the expand icon at the right of the SubmittedBy header:

The Expenses query with five rows whose SubmittedBy column shows [List], and the expand icon in the SubmittedBy header outlined in red

In the example, Power Query expanded the list to new rows straight away, with no menu. Each list holds one person, so there's still one row per expense, and each cell now shows [Record]:

Expanded SubmittedBy
Table.ExpandListColumn(#"Renamed columns", "SubmittedBy")

The same query after the step Table.ExpandListColumn(#"Renamed columns", "SubmittedBy"), with SubmittedBy showing [Record] on all five rows

Microsoft's Table.ExpandListColumn reference says it splits the list into a row for each value, duplicating the other columns, so a list of two people would make two rows.

Step 2: Pick the Email Field​

Select the expand icon again. A box lists the record's fields: id, title, email, sip, picture, department and more. Untick (Select all), tick email, and untick Use original column name as prefix, so the new column is called email rather than SubmittedBy.email:

The expand box over the SubmittedBy column with the fields id, title, email, sip, picture and department, only email ticked and outlined in red, and Use original column name as prefix unticked

Select OK. The column becomes email, with each person's address:

Expanded SubmittedBy (the record)
Table.ExpandRecordColumn(#"Expanded SubmittedBy", "SubmittedBy", {"email"}, {"email"})
  • {"email"} is the fields to take from each record.
  • The second {"email"} is the new columns' names.

The Expenses query with an email column of addresses such as aisha.khan@example.com and admin@example.com, a Table.ExpandRecordColumn formula that takes the email field, and two Expanded steps under Applied steps

A Column with Several People​

The Managers column of Expense Categories shows [Table]: one row per person. Its expand icon opens the same kind of box, with the fields id, value, title, email, sip and picture. Tick only email, untick the prefix option, and select OK:

The expand box over the Managers column, whose cells show [Table], with id, value, title, email, sip and picture, only email ticked and outlined in red, and Use original column name as prefix unticked

This time one step does it:

Expanded Managers
Table.ExpandTableColumn(#"Choose columns", "Managers", {"email"}, {"email"})

The three categories become four rows, because Meals has two managers:

A query named Category managers with the rows Travel olivia.bennett@example.com, Office admin@example.com, Meals olivia.bennett@example.com and Meals daniel.brooks@example.com, and a Table.ExpandTableColumn formula that takes the email field of Managers

The example did this in a copy of the query, Category managers, so the original Expense Categories keeps one row per category (How to Duplicate a Query in Power Query).

The new email columns have the ABC 123 icon, any type. Give each a clearer name, such as SubmitterEmail or ManagerEmail (How to Rename a Column in Power Query), and the Text type (How to Change a Column's Data Type in Power Query).

Common Mistakes​

MistakeWhat happensDo this instead
Expanding a multi-person column in the main tableRows with several people are repeated, and totals double countExpand it in a separate query
Leaving the prefix option tickedThe column is called SubmittedBy.emailUntick Use original column name as prefix
Taking title for matching peopleNames aren't unique and can changeemail
Leaving the new column as any typeThe model guesses its typeSet Text

Troubleshooting​

SymptomLikely causeFix
The expand box lists no emailThe column isn't a Person columnCheck the column's type in SharePoint
Some emails are emptyThe item has nobody in the columnFilter out empty emails if you need to
More rows than itemsThe column holds several peopleExpected for a multi-person column; use a separate query

FAQ​

Why does one person come in as a list?​

That's how implementation 2.0 of the connector returned it in the example: a list with one record. Expanding the list, then the record, gives one email per row.

Can I get the person's name too?​

Yes: tick title as well as email in the expand box.

Does this work in Power BI Desktop?​

Yes. The expand icon and the three functions are the same in every Power Query.

Conclusion​

A single-person column: expand the list, then pick email from the record. A multi-person column: expand the table and pick email, which makes one row per person. Untick the prefix option, rename the column and set it to Text.

Additional Resources​