Forum Discussion

Magdoulin's avatar
Magdoulin
New Member
3 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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 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
Icon for Community Champion rankCommunity Champion
3 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.