group by a column and the group item to be shown in comma separated in single column

[mmoizk]

Have to group by a column and have the group items in coma seprated list in a cloum .

I am importing a sharePoint online list data in my powerBI and the data looks like below

ID          Name

1            Pen

2            Chair

2            Table

3            Plug

3            Power

3            shocket

4           Board

4            Marker

4           Eraser

4           Clip

This data need to be tranlate to 

ID            New Column

1             Pen

2             Chair, table 

3             Plug, Power, shocket

4              Board, Marker, Eraser, Clip

[Sean · Accepted solution]

@mmoizk You'll have to simplify this... But here it is... Full Credit to @ImkeF

http://community.powerbi.com/t5/Desktop/Help-with-a-table-visualization/td-p/24143/page/3

[Sean]

@mmoizk Try creating a new column with CONCATENATEX

= CONCATENATEX ( FILTER ( VALUES (Table[Name])  ), Table[Name], ", ")

Let me know if this works.

[mmoizk]

Thanks Sean,

I was trying to do this at ETL time so i have in my model as a dimension table which will then join to a Fact table that have this ID . If i understood it correctly you are asking me to do a Dax on the model . 

[Sean · Accepted solution]

@mmoizk You'll have to simplify this... But here it is... Full Credit to @ImkeF

http://community.powerbi.com/t5/Desktop/Help-with-a-table-visualization/td-p/24143/page/3

[mmoizk]

Thanks Sean, this is close to what i was looking. But i am getting some syntax erros. I have abondon that for now. I am working on the DAX for concatenation. The one you posted earlier , for some reason its not working for me. Let me state the problem again.

Dataset1

 ID           Tran       

1              T1

2              T2

3              T3

Dataset2

Dataset1.ID         Name

1                          Chair

1                          Table

1                          Board

2                         Nail

2                          Hammer 

3                          Toolbox

In a Table display i would like

1      Tran1      Chair,Table,Board

2      Tran2      Nail, Hammer

3      Tran3      Toolbox 

So if i want to use = CONCATENATEX ( FILTER ( VALUES (Table[Name])  ), Table[Name], ", ")

In dataset1 i do a new column 

List = = CONCATENATEX ( FILTER ( VALUES (Dataset2[Name])  ), Dataset2[Name]), ", ")

[Sean · Accepted solution]

@mmoizk this works as a Measure

and you actually don't need the FILTER funciton (I was filtering out Names)

Measure = CONCATENATEX ( VALUES ( Table2[Name] ), Table2[Name], ", " )

In a Table Visualization turn off the Totals as in the picture

Otherwise Total row will concatenate ALL names

[MRRAC]

Works! Used in a product import that requires that all categories (from a one to many relationship) are in the same field separated by comma.

Thank you!

[Ranneh]

This is very helpful, thank you! @Sean

[mmoizk]

Thanks Sean, it worked. I am getting  desired results. I though measure is always a numeric. Just curious what else is possible with string/text as a measure ? Like here we concatenating string.