Forum Discussion
Converting table rows to a comma separated field
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
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
2 Replies
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
Anonymous , You can create a measure
measure = concatenateX(Table, Table[data], ",")
and use that is visual with id column
