Forum Discussion
Extracting Email Addresses from a Table Column Based on a Person Data Type
Community SupportHello 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.
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 😁!