Forum Discussion

ronaldwanat's avatar
ronaldwanat
Frequent Visitor
1 year ago

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 d...
Need Help
ronaldwanat's avatar
ronaldwanat
Frequent Visitor
1 year ago

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's avatar
v-ssriganesh
Community Support
1 year ago

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's avatar
    ronaldwanat
    Frequent Visitor
    1 year ago

    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's avatar
      v-ssriganesh
      Community Support
      1 year ago

      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's avatar
        ronaldwanat
        Frequent Visitor
        1 year ago

        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 😁!