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