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

[kladkent]

 Hello Guys!

I need to create a custom column that combines all the "ITEM" with the same "ID".

Thanks,

[BA_Pete · Accepted solution]

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

[BA_Pete]

You're welcome.

Don't forget to give a thumbs-up on any posts that have helped you 👍

Pete