Extract unique words from a text separated by comma

[rashidanwar]

Hi everybody

Hope that you are doing great.

I have a long string containing repetitive words separated by comma. I want to remove the duplicates and retain only unique words.

For example:  John,Arthur,John,Lisa,Patrick,John,Lisa
Output should be: John,Arthur,Lisa,Patrick

I would appreciate for ant help.

Best Regards
Rashid

[v-jayw-msft · Accepted solution]

Hi @rashidanwar ,

You will need to split the columns in to rows first in Power Query Editor and then you could use the below DAX formula to get the result.

Column = CONCATENATEX (
VALUES ( 'Table'[Column1] ),
'Table'[Column1] ,
", "
)

Best Regards,
Jay

[rashidanwar]

Thank you so much for your reply.
This text is the result of a calculated column in the Data view. Doesn't exits in the query editor.

[amitchandak]

@rashidanwar , In power query create a new column like

Text.Combine(List.Sort(List.Distinct( Text.Split([Column]))), ",")

[Benito_Carroll]

almost 😉

Text.Combine(List.Sort(List.Distinct( Text.Split([Column],","))), ",")