Rows to List

[StoryofData]

Hello, 

How do I convert my table into a list based on selected values?

This is what I have 

Identification Number	Category	All Other columns
A-123	Apple
A-123	Mango
A-123	Banana
B-456	Pineapple
B-456	Watermelon

What I need

Identification Number	Category
A-123	Apple, Mango, Banana
B-456	Pineapple, Watermelon

[edhans · Accepted solution]

Hi, you want to use the Table.Group feature, but you'll use a custom aggregation. I created it using a COUNT aggregation, then replaced it with Text.Combine. The _ represents the table (first one has 3 rows, 2nd one has 2 rows). The [Category] turns it to a list for the category field, and Text.Combine operates on that. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctQ1NDJW0lFyLCjISQXSSrE6CEHfxLz0fHRBp8Q8IISJOumamJoBOQGZeamJyGbAJMITS1KLclNz8vPAMrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Identification Number" = _t, Category = _t, #"All Other columns" = _t]),
    #"Grouped Rows" = 
        Table.Group(
            Source, 
            {"Identification Number"}, 
            {
                {"List", each Text.Combine(_[Category], ", "), type text}
            }
        )
in
    #"Grouped Rows"

 How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[StoryofData]

thank you so much!

[edhans]

Glad I could help @StoryofData