Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Concatenate 2 columns

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

Column1Column2
1Apple
2Apple
3Apple
4Apple
5Carrot
6Carrot
7Carrot

 

I want the Output like below

 

Column1Column2Column3
1Apple1,2,3,4
2Apple1,2,3,4,
3Apple1,2,3,4,
4Apple1,2,3,4,
5Carrot5,6,7
6Carrot5,6,7
7Carrot5,6,7

 

Thanks.

Paruchuri

Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    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's avatar
    MarcelBeug
    8 years ago

    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:

5 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      MarcelBeug
      Icon for Community Champion rankCommunity Champion
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        I found the answer.

         

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