Forum Discussion
Extracting Email Addresses from a Table Column Based on a Person Data Type
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.
19 Replies
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.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
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.
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.
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
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.
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.
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.
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
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.
