Converting table rows to a comma separated field

[Anonymous]

I have a table which is made up of two columns.

Column A = ID

Column B = Data

For each ID there may be multiple rows of data e.g.

ID	Data
1	Fred
1	Sue
1	Ade
2	Sam
2	Joy

What I want to do is somehow get the data to be as follows:

ID	Data
1	Fred, Sue, Ade
2	Sam, Joy

The data is unfortunately presented by the API service in an XML format which means the data is presented as in the first instance. I have my query pulling the data down but could do with a way to then convert it into the desired format.

Preferably this would be done in m query but if needed, I could do the manipulation in DAX so either solution will work for me.

Any help much appreciated

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are two methods you can use:

Dax:

Create calculated table.

Table =
SUMMARIZE('Table1',Table1[ID],"2",CONCATENATEX('Table1','Table1'[Data],","))

Power query:

Enter into the power query, Home – Group by

This time throws an error, Power Query performs a Sum operation on the text value, so this error occurs. Apparently, the Sum operation only works on numbers and doesn't concatenate text values.

Copy the following code in:

= Table.Group(#"Changed Type", {"ID"}, {{"New data", each Text.Combine([Data] ,","), type text}})

Please click here for the pbix file

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , You can create a measure

measure = concatenateX(Table, Table[data], ",")

and use that is visual with id column