Forum Discussion
Agregate multiple names into one cell
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 |
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]), ", ")Thank you SundarRaj - its working 🙂
4 Replies
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
Thank you SundarRaj - its working 🙂
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 AdvanceYou 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]), ", ")
