row duplication when expanding a table column

[akropp94]

In query when expanding a table column, rows duplicate like this:

becomes

I understand why this happens, but is there any way to instead of duplicating the rows, have it so that it does this instead: 

 

any sort of solution that does this would be appreciated - including using DAX or something. (It doesnt have to be a comma, just did that for the example)

My data source is sharepoint online - Column 2 source is a choice column which allows multiple values, hence the table. for each row.

Regards

Ariel Kropp

[radpir · Accepted solution]

Hi,

Try adding a column in Power Query before expanding the Column 2:

= Text.Combine(Table.Column([Column 2], "Fruit"), ", ")

where "Fruit" is the name of the column in the table that is concatenated and needs to be changed to match your data setup.

Hope this helps,

radpir

[akropp94]

worked brilliantly.

thank you

[Greg_Deckler]

Create a measure in DAX like:

Measure = CONCATENATEX(VALUES(Apples[Column2]),[Column2],",")

Create a table visualization of Column1 and this measure.

[akropp94]

This did not work - when creating a measure it did not allow this expression, many errors.