Agregate multiple names into one cell

[DanFromMontreal]

Heelo dear community,

I'm struggling to develop the code to obtain the 3 output below.

My table contains 2 columns... Fruit and Name.

It surely as to do with List but cannot make it work.

Help would be appreciated.

		Output 1	Output 2	Output 3
Fruit	Name	Count Fruit Frequency	Customer (no duplication)	Customer (w/duplication)
Apple	Dan	2	Dan , Irene	Dan , Irene
Banana	Justin	1	Justin	Justin
Apple	Irene	2	Dan , Irene	Dan , Irene
Pear	Dan	4	Dan, Bob, Jack	Dan, Bob, Jack, Dan
Pear	Bob	4	Dan, Bob, Jack	Dan, Bob, Jack, Dan
Pear	Jack	4	Dan, Bob, Jack	Dan, Bob, Jack, Dan
Pear	Dan	4	Dan, Bob, Jack	Dan, Bob, Jack, Dan

[ronrsnfld · Accepted solution]

You can add Custom Column:

For Customer with duplications, in the Custom Column dialog box:

Text.Combine(
Table.SelectRows(#"Changed Type", (r)=>r[Fruit]=[Fruit])[Name], ", ")

For Customer with no duplications:

Text.Combine(List.Distinct(
Table.SelectRows(#"Changed Type", (r)=>r[Fruit]=[Fruit])[Name]), ", ")

[ronrsnfld · Accepted solution]

You can add Custom Column:

For Customer with duplications, in the Custom Column dialog box:

Text.Combine(
Table.SelectRows(#"Changed Type", (r)=>r[Fruit]=[Fruit])[Name], ", ")

For Customer with no duplications:

Text.Combine(List.Distinct(
Table.SelectRows(#"Changed Type", (r)=>r[Fruit]=[Fruit])[Name]), ", ")

[Akash_Varuna]

Hi @DanFromMontreal , You could Achive this with Power Query with Group By Try this please

1.Count Fruit Frequency
Table.Group(#"PreviousStep", {"Fruit"}, {{"Count Fruit Frequency", each Table.RowCount(_), Int64.Type}})

2. Customer (No Duplication)
Table.Group(#"PreviousStep", {"Fruit"}, {{"Customer (no duplication)", each Text.Combine(List.Distinct([Name]), ", "), type text}})

3. Customer (With Duplication)
Table.Group(#"PreviousStep", {"Fruit"}, {{"Customer (w/duplication)", each Text.Combine([Name], ", "), type text}})

If this post helped please do give a kudos and accept this as a solution
Thanks In Advance

[SundarRaj]

Hi @DanFromMontreal , here's the solution to your query. I'll attach the output image and the snippet of the M code used below for reference. Thanks

[DanFromMontreal · Accepted solution]

Thank you SundarRaj - its working 🙂