Extracting Email Addresses from a Table Column Based on a Person Data Type

[ronaldwanat]

How can I extract email address when using Power Query from a Person data type column that is a Table type?  This column may have more than one person and I want them all extracted in the same cell delimited by commas.  Also the data source is a SharePoint list.

[v-ssriganesh]

Hello @ronaldwanat,
I am following up to see if you had a chance to review my previous response and provide the requested information. This will enable us to assist you further.

Thank you.

[ronaldwanat]

Hi  and thanks.

Yes, I did see your proposal however I am going to decline at this time.  I will be doing some of my own research on this topic in the hopes I can find a solution.

Thanks anyway.

Regards,

Ronald Wanat

[v-ssriganesh]

Hi @ronaldwanat,

We greatly appreciate your efforts and thank you for providing the update on the issue. If the issue has been resolved, kindly share the insights, as this will assist other community members in resolving similar issues more efficiently.

Thankyou.

[ronaldwanat]

Sure.  Will do.  Once I come up with the Solution I will post it here for all to see.

[v-ssriganesh]

Hello @ronaldwanat,
Hope everything’s going great on your end. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[ronaldwanat]

Hi,

I added the code you provided as a step in the Power Query Editor and got the following error message.  FYI, The column name in my SharePoint list is "Testers".  That is the only thing I changed.

[v-ssriganesh]

Hello @ronaldwanat,
Thank you for your response and for sharing the error screenshot.

The issue you're encountering “Expression.Error: The name 'ExpandedRecords' wasn't recognized” occurs because the variable ExpandedRecords was used in the Table.Group() step but wasn’t explicitly defined in a prior step.

In Power Query, each step refers to the result of the previous step using its step name (seen on the right under APPLIED STEPS). If your list of email records was expanded in the previous step (e.g: Custom1), you should refer to that step name directly.

In your Custom2 step, replace ExpandedRecords with the actual name of your previous step likely Custom1.

So, the corrected code will look like:

= Table.Group(Custom1, {"ID"}, {

    {"Testers", each Text.Combine([Email], ", "), type text}

})

Make sure that:

• Custom1 is the correct step where the Email column exists.
• The column Email is already extracted from the JSON or record expansion step.

Please try this adjustment and let me know if you encounter any issues.

Best Regards,
Ganesh singamshetty.

[ronaldwanat]

Hi @v-ssriganesh,

And thanks again for your help.

I made it a bit further then received another error message.  However, more importantly, I did not realize you provided the complete code set:

So regarding  Line#2:

"Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY67CgJBDEV/JaQeBLWzEnwUsoqwdjNTBAnM6DwWH7Ai/rvBzihb5oRz77UWx2jQPh3uKLPDmcNNDQWWVQ7jcJUppg8+CZ5zT7lLPDrW7PBlvjQqDG2Ot6BFeSjRozcWJ7p5G88M69qrgCz4b8BUB7R0IWhYT78KHpq+p3uCAxWldYKHtCamBywCay8J/93r3w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Approvers = _t]),"

that won't work for me.  My Power BI defaults to the following Source format.  

let
    Source = SharePoint.Tables("https://www.sharepoint.com/sites/123456/ABCDE/", [Implementation="2.0", ViewMode="All"]),
    #"bd29e903-9cfb-4deb-bc7a-789c5c6a7eb0" = Source{[Id="bd29e903-9cfb-4deb-bc7a-789c5c6a7eb0"]}[Items]
in
    #"bd29e903-9cfb-4deb-bc7a-789c5c6a7eb0"

Do you know how I would convert Line#2 above to conform with how my Power BI access the SharePoint list?

Thanks,

Ron 😁

[v-ssriganesh]

Hello @ronaldwanat,
Thanks for your reply and for sharing the SharePoint list source details.

You're right, the sample I initially shared uses inline sample data (via Binary.Decompress) for quick reproduction purposes. Since you're connecting to a live SharePoint List, we can absolutely adapt the M code to work with your source. Please try below m query:

let

    Source = SharePoint.Tables("https://www.sharepoint.com/sites/123456/ABCDE/", [Implementation="2.0", ViewMode="All"]),

    Approvals = Source{[Id="bd29e903-9cfb-4deb-bc7a-789c5c6a7eb0"]}[Items],

    ChangedType = Table.TransformColumnTypes(Approvals, {{"ID", Int64.Type}, {"Approvers", type text}}),

    Parsed = Table.AddColumn(ChangedType, "ParsedApprovers", each Json.Document([Approvers])),

    ExpandedList = Table.ExpandListColumn(Parsed, "ParsedApprovers"),

    ExpandedRecords = Table.ExpandRecordColumn(ExpandedList, "ParsedApprovers", {"Name", "Email"}),

    Grouped = Table.Group(ExpandedRecords, {"ID"}, {

        {"AllEmails", each Text.Combine([Email], "; "), type text}

    })

in

    Grouped

Best Regards,
Ganesh singamshetty.

[ronaldwanat]

Hi @v-ssriganesh ,

Here is the code I executed.  I renamed two locations where in your example you have Approvers to Testers.  I assumed the code was looking for the name of the column of the people / table data type.

let

    Source = SharePoint.Tables("https://www.sharepoint.com/sites/123456/ABCDEF/", [Implementation="2.0", ViewMode="All"]),

    Approvals = Source{[Id="bd29e903-9cfb-4deb-bc7a-789c5c6a7eb0"]}[Items],

    ChangedType = Table.TransformColumnTypes(Approvals, {{"ID", Int64.Type}, {"Testers", type text}}),

    Parsed = Table.AddColumn(ChangedType, "ParsedApprovers", each Json.Document([Testers])),

    ExpandedList = Table.ExpandListColumn(Parsed, "ParsedApprovers"),

    ExpandedRecords = Table.ExpandRecordColumn(ExpandedList, "ParsedApprovers", {"Name", "Email"}),

    Grouped = Table.Group(ExpandedRecords, {"ID"}, {

        {"AllEmails", each Text.Combine([Email], "; "), type text}

    })

in

    Grouped

The Navigation step seems to work fine.  So far, so good...

 

The ChangedType step generates a type mismatch error. It is saying, essentially, you cannot convert a Table type to a Text type.

 

Thank you very much for looking into this.  I think we are getting closer to the solution 😁!

[v-ssriganesh]

Hi @ronaldwanat,
Thanks again for your response and detailed screenshots they were very helpful.

You're correct that the Testers column is coming through as a Table type directly from the SharePoint List connection (rather than a JSON string). That means there's no need to convert it or parse it using Json.Document.

Please try below code:

let

    Source = SharePoint.Tables("https://www.sharepoint.com/sites/123456/ABCDEF/", [Implementation="2.0", ViewMode="All"]),

    Approvals = Source{[Id="bd29e903-9cfb-4deb-bc7a-789c5c6a7eb0"]}[Items],

    ExpandedList = Table.ExpandListColumn(Approvals, "Testers"),

    ExpandedRecords = Table.ExpandRecordColumn(ExpandedList, "Testers", {"Name", "Email"}),

    Grouped = Table.Group(ExpandedRecords, {"ID"}, {

        {"AllEmails", each Text.Combine([Email], "; "), type text}

    })

in

    Grouped

This should now work without any errors and produce the expected output a single row per ID with the list of emails.

Let me know if this resolves it.

Best Regards,
Ganesh Singamshetty

[ronaldwanat]

Hi @v-ssriganesh,

Thanks again for your support.

The code did run without error, but I did not get the expected output.  Instead this what resulted:

1. All columns from the original query were removded and I was only left with two colums (ID and AllEmails)
2. The AllEmails column was blank with no email addresses.  The total row count was correct though.

Here is the updated code I used:

let

    Source = SharePoint.Tables("https://www.sharepoint.com/sites/123456/ABCDEF/", [Implementation="2.0", ViewMode="All"]),

    Approvals = Source{[Id="bd29e903-9cfb-4deb-bc7a-789c5c6a7eb0"]}[Items],

    ExpandedList = Table.ExpandListColumn(Approvals, "Testers"),

    ExpandedRecords = Table.ExpandRecordColumn(ExpandedList, "Testers", {"Name", "Email"}),

    Grouped = Table.Group(ExpandedRecords, {"ID"}, {

        {"AllEmails", each Text.Combine([Email], "; "), type text}

    })

in

    Grouped

Following are the list of steps and results.  Hopefully this can help.

So far, so good.  Testers (people type) is a Table type. Note row number two is unique.  Total count of 70 columns and 177 rows is correct.

I expanded one Tester cell and as you can see above it has two people associated with it.  My problem from day one, is to extract thoses emails, seperated by commas semicolons but keep all other columns intact (unless we need to replace the Testers columns with the emails, that is fine).

 

Testers people data type column has been expanded and now displays as a Record type.  70 rows and 328 columns now. In the sample above you can see rows 2 and 3 were expanded to account for the two persons associated with CoCo Control ID 75.1.01.

The Testers column was replace with Name and Email.  That is fine, but null is not accurate.  These do have people and email addresses which should display.

The row count of 177 makes sense, but all columns have been removed.  I need them in place to do my analysis.

And the final steps shows AllEmails blank which is not correct and all other columns are missing.

Thanks again for your support and looking forward to coming up with the final solution.

Regards,

Ronald Wanat

[v-ssriganesh]

Hello @ronaldwanat,
Thanks for getting back.

To help you further, could you please provide sample data that clearly illustrates the issue? Kindly ensure the sample data:

• Covers the problem scenario completely
• Is shared in a usable format (not as screenshots)
• Does not include any sensitive or unrelated information
• Includes the expected outcome based on your sample

If you're not sure how to share sample data, here’s a helpful guide:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Once we have that, we’ll be better able to reproduce the issue and assist you with an accurate solution.

Best regards,
Ganesh Singamshetty

[ronaldwanat]

Hi @v-ssriganesh ,

I am doing well, thanks for asking!  I will be working on implementing the proposed solution this week and will most certainly provide you with an update on how it turns out!

Regards,

Ron

[v-ssriganesh]

Hello @ronaldwanat,
Thank you for reaching out to the Microsoft Fabric Community Forum.

I’ve reproduced your scenario using Power BI Desktop and simulated a SharePoint List Person-type column that allows multiple people per row. Since such columns are internally structured as nested tables (or JSON when accessed through certain connectors), there’s no direct “Parse JSON” or “To Table” option in Power Query UI. However, this can be solved using Power Query M code.

Steps Used:

Parsed the JSON in the Approvers column using:

Json.Document([Approvers])​

• Expanded the list to rows.
• Expanded each record to extract Name and Email.

Grouped by ID and used:

Text.Combine([Email], ", ")​

I’ve attached the working .pbix file demonstrating the entire transformation from source to final output.

Output :

Best Regards,
Ganesh singamshetty.

[ronaldwanat]

Hi Ganesh,

Thank you so very much!  This is exactly the outcome I was looking for!👍

Please allow me a few days to test this out before marking as Solution.😀

Regards,

Ronald Wanat

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.