Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

 

IDData
1Fred
1Sue
1Ade
2Sam
2Joy

 

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

 

IDData
1Fred, Sue, Ade
2Sam, 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

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

Here are two methods you can use:

Dax:

Create calculated table.

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

vyangliumsft_0-1649725694242.png

Power query:

Enter into the power query, Home – Group by

vyangliumsft_1-1649725694248.png

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.

vyangliumsft_2-1649725694249.png

Copy the following code in:

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

vyangliumsft_3-1649725694249.png

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

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

Here are two methods you can use:

Dax:

Create calculated table.

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

vyangliumsft_0-1649725694242.png

Power query:

Enter into the power query, Home – Group by

vyangliumsft_1-1649725694248.png

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.

vyangliumsft_2-1649725694249.png

Copy the following code in:

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

vyangliumsft_3-1649725694249.png

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
Super User
Super User

@Anonymous , You can create a measure

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

 

and use that is visual with id column

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.