Concatenate 2 columns

[Anonymous]

Hello Community,

I have 2 Columns one column is with numbers and second column contains unique text values. I want to add comma separate to first column based on second column. 

I'm very new to power bi and just learning it.

Table-1

Column1	Column2
1	Apple
2	Apple
3	Apple
4	Apple
5	Carrot
6	Carrot
7	Carrot

I want the Output like below

Column1	Column2	Column3
1	Apple	1,2,3,4
2	Apple	1,2,3,4,
3	Apple	1,2,3,4,
4	Apple	1,2,3,4,
5	Carrot	5,6,7
6	Carrot	5,6,7
7	Carrot	5,6,7

Thanks.

Paruchuri

[Vvelarde · Accepted solution]

@Anonymous

Hi, try with this calculated column

Column =
CONCATENATEX (
    SUMMARIZE (
        FILTER ( Table2, Table2[Fruit] = EARLIER ( Table2[Fruit] ) ),
        Table2[ID]
    );
    Table2[ID],
    ","
)

Regards

Victor

Lima - Peru

[Vvelarde · Accepted solution]

@Anonymous

Hi, try with this calculated column

Column =
CONCATENATEX (
    SUMMARIZE (
        FILTER ( Table2, Table2[Fruit] = EARLIER ( Table2[Fruit] ) ),
        Table2[ID]
    );
    Table2[ID],
    ","
)

Regards

Victor

Lima - Peru

[MarcelBeug · Accepted solution]

A Power Query solution:

let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"Column2"}, {{"Column3", each Text.Combine([Column1],","), type text}, {"AllRows", each _, type table}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Column1"}, {"Column1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded AllRows",{{"Column1", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column1", "Column2", "Column3"})
in
    #"Removed Other Columns"

This is how the code was created:

[Anonymous]

I found the answer.

CALCULATE(CONCATENATEX(Tablename,Tablename[column-1],","),FILTER(Tablename,Tablename[Column-2]=EARLIER(Tablename[Column-1])))

[MarcelBeug]

Congratulations.

Typically there are more answers possible, though,

I hopy you don't mean our answers were incorrect?

[Anonymous]

I don't mean your answer is incorrect. We can do that way as well. Thanks for the solution.