Forum Discussion

Magdoulin's avatar
Magdoulin
New Member
2 years ago
Solved

Grouping Emails

Hello everyone,

 

I need a little help here. I have a list of users and emails, each user could have inconsistent number of emails, e.i. some might have 2, emails whereas the other might have 3, 4 or even 1 email, no rule here, below is a sample of how the data looks like:

 

I want to group the emails based on the persons as follows:

 

So, what is the code to use here?

 

P.S. please pay special attention to the separator ", ".

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Magdoulin 

    You can put the following code to advanced editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PS8xRUDBU0lFKzU3MzDHUM3QAM/SS83OVYnVwKDIiRpExTkVGMEVGeKxDUoRiXSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Email = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Email", type text}}),
    Custom1 = Table.Group(#"Changed Type", {"Person"}, {{"Emails", each Text.Combine(List.Sort([Email]), " , " ), type text}})
in
    Custom1

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Magdoulin 

    You can put the following code to advanced editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PS8xRUDBU0lFKzU3MzDHUM3QAM/SS83OVYnVwKDIiRpExTkVGMEVGeKxDUoRiXSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Email = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Email", type text}}),
    Custom1 = Table.Group(#"Changed Type", {"Person"}, {{"Emails", each Text.Combine(List.Sort([Email]), " , " ), type text}})
in
    Custom1

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • foodd's avatar
    foodd
    Community Champion
    2 years ago

    What seems missing is an Index of EmployeeID or EmployeePayrollID and establishing a tie

    to the User Principal Name (UPN), and tie back to the email alias. 

     

    Your Mail Admin can help you pull this, and perhaps provide a PowerShell Script 

    which pulls all of this together into a CSV or helps with the Power Query

    script connecting to Active Directory that also pulls this together.