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

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

    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

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