Rows to List

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.