Custom Column in Power query, Returns column values from different rows with same column ID

Hi kladkent ,

 

Try this example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJydFeK1YGwfRwDQvwDwFwjINfVMSjAw9/PFS4Q4Orn7OmD4MIljYE8b9dIJ3/HIBe4gLOHo2eQUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, ITEM = _t]),

    groupRows = Table.Group(Source, {"ID"}, {{"data", each _, type table [ID=nullable text, ITEM=nullable text]}, {"CUSTOM COLUMN", each Text.Combine([ITEM], ", "), type nullable text}}),
    expandDataCol = Table.ExpandTableColumn(groupRows, "data", {"ITEM"}, {"ITEM"})
    
in
    expandDataCol

 

You basically group your table on [ID] and add an 'All Rows' aggregated column and a 'SUM' column on [ITEM].

This obviously gives an error, so you adjust your Group By code to change List.Sum([ITEM]) to Text.Combine([ITEM], ", ").

You then expand the [ITEM] column back out from the nested All Rows column.

 

Example output:

 

Pete