Grouping Emails

[Magdoulin]

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 ", ".

[v-xinruzhu-msft · Accepted solution]

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]

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.