Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Magdoulin
New Member

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:

Magdoulin_0-1694621198302.png

 

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

Magdoulin_1-1694621225573.png

 

So, what is the code to use here?

 

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

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1694755381392.png

 

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.

View solution in original post

2 REPLIES 2
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1694755381392.png

 

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
Super User
Super User

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors