Forum Discussion
Distinct on recurring value (column), while concatenating its unique value (column)
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!
you can try this
pet2 = CONCATENATEX(DISTINCT('Table'[Pet]),'Table'[Pet],";")name2 = CONCATENATEX(DISTINCT('Table'[Name]),'Table'[Name],";")
