Remove duplicate values in cells using Power Query

[programsam]

Greetings,

I'm not looking to remove duplicates in a column but when joining tables, I have some where multiples of the same values are concatenated in a single cell. Example:

I Want for the first row to read Tom, Sam and omit the second (and any additional) instances of "Sam". How would this be achieved?

 

[ExcelToDAX]

If you have Office 365, and assuming Tom, Sam, Sam is in Cell A1, you could use this Excel formula:

=TEXTJOIN(", ",TRUE,UNIQUE(TEXTSPLIT(A1,,", ")))

For Power Query, you can reference this article: Concatenate Text with Group By (Combine Rows) in Power Query

 

[alansidman]

Maybe:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Column1.1", "Column1.2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column1.3"})
in
    #"Removed Columns"

 

[smozgur]

Try the following. Comments would help to see what's going on.
Change the source with the actual source since it is using binary sample data.

let
    // I used a binary data sample. You need to change this with your own source. This is only for testing the code quickly.
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnP1VEIToQQSrE6YBEwDeODJcGCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t]),
    // Add a new column creating a list from the names split by usung the Text.Split() function
    // Then simply use the List.Disctinct() function to remove the duplicates from the list
    AddColumn = Table.AddColumn(Source, "Unique Names", each List.Distinct(Text.Split([Names], ","))),
    // Remove the original data column named as Names in my sample data
    RemovedOriginal = Table.RemoveColumns(AddColumn,{"Names"}),
    // Concatenate distinct list items by using the Text.Combine() function 
    // (The inner List.Transform() function in this step only makes sure converting the list items to text data type 
    Result = Table.TransformColumns(RemovedOriginal, {"Unique Names", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    Result

Data:

After transformation:

 

[JEC]

Or a direct transformation in the same column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnP1VEIToQQSrE6YBEwDeODJcGCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t]),
    result = Table.TransformColumns(Source,{{"Names", each Text.Combine(List.Distinct(Text.Split(_,", ")),", "), type text}})
in
    result

 

[programsam]

Or a direct transformation in the same column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnP1VEIToQQSrE6YBEwDeODJcGCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t]),
    result = Table.TransformColumns(Source,{{"Names", each Text.Combine(List.Distinct(Text.Split(_,", ")),", "), type text}})
in
    result

This worked great, thank you for the assist!

 

[JEC]

You’re welcome!