Distinct on recurring value (column), while concatenating its unique value (column)

[Anonymous]

Good day all,

I want to trim a dataset be removing repeats of a certain column, like a distinct function. However, in another column there are unique values, which I would all like to keep, by concatenation. 

Example:

A dataset that looks like this:

Owner	City	Pet	Name
Bob	Berlin	Dog	Chippy
Bob	Berlin	Cat	Ziggy
Bob	Berlin	Dog	Jiffy
Judy	New York	Cat	Taco
Judy	New York	Cat	Baco

Would become the following:

Owner	City	Pet	Name
Bob	Berlin	Dog; Cat; Dog	Chippy; Ziggy; Jiffy
Judy	New York	Cat; Cat	Taco; Baco

Does anyone know how I would go about this? I can imagine a concatenation before using distinct would be a valid approach. However, I do now know how to do this. Help is much appreciated!

[ryan_mayu · Accepted solution]

you can try this

pet2 = CONCATENATEX(DISTINCT('Table'[Pet]),'Table'[Pet],";")

name2 = CONCATENATEX(DISTINCT('Table'[Name]),'Table'[Name],";")

[Anonymous]

Thank you!

[ryan_mayu]

you are welcome