How can I combine on a group by ?

[EaglesTony]

hi,

  I have the following:

  Application Code    Info

  401                          Doe, John|

  401                          Doe, Jane|

  402                          Trust, Frank|

I need it:

  Application Code    Info

  401                          Doe, John|Doe, Jane|

  402                          Trust, Frank|

I'm thinking a group by as some sort ?

[SundarRaj · Accepted solution]

Hi @EaglesTony 
while grouping, you can simply pull the column that you want _[ColumnName] which will convert it into a list. Post this, just wrapping it around Text.Combine() shall do the trick. Thanks

[ronrsnfld]

Use Table.Group with a custom aggregation (write it in the Advanced Editor):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwVNJRcslP1VHwys/Iq1GK1UEVTMxLhQkaAQVDikqLS3QU3IoS87KB4rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Application Code" = _t, Info = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Application Code", Int64.Type}, {"Info", type text}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Application Code"}, {
        {"Info", each Text.Combine([Info],""), type text}})
in
    #"Grouped Rows"

Original

Result

Note that if your original data did not have the vertical bar at the end of each name, you could use that as the delimiter in the Text.Combine function.

[bhanu_gautam]

@EaglesTony 

In Power Query Editor, select the "Application Code" column.
Go to the "Home" tab and click on "Group By".
In the Group By dialog, set the following:
Group by: "Application Code"

New column name: "Concatenated Info" (or any name you prefer)
Operation: "All Rows" (this will create a table for each group)

Concatenate the Info:

After grouping, you will have a new column with tables. Click on the small expand icon in the header of this new column and select "Info" to expand it.
Add a custom column to concatenate the "Info" values. Go to "Add Column" > "Custom Column" and use the following formula:

     Text.Combine([Info], "|")

This will concatenate all "Info" values for each "Application Code" with a "|" separator.

[EaglesTony]

I tried this and created the new column as "Info":

I then expanded this, and added this:

But I get an error:

Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type]