Filter out unique values from the cell that contains multiple values

[arinyc]

Hi,

I have a column that contains multiple values and I want to create a new column that keeps the unique values only from the original one. 

Example:

- if the multiple value is "Springfield, Springfield, Pueblo West", the new column should show "Springfield, Pueblo West".

- if the multiple value is "Springfield, Springfield, Springfield", the new column should show "Springfield".

Is there a way to create a calculated column that solves this?

This is the mage of the column and wanted result in red colour.

Best,

[lazarjo · Accepted solution]

You can solve this by adding a custom column in Power Query. On the "Add Column" tab click on Custom Coulmn option and type the next code :

Text.Combine( List.Distinct( List.Transform( Text.Split([City],","), each Text.Trim(_))),",")

The result will be unique values separated by comma in each cell.

Best

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.Distinct(Text.Split([City],", "),Comparer.OrdinalIgnoreCase),", "))
in
    #"Added Custom"

Hope this helps.

[v-yangliu-msft]

Hi  @arinyc ,

Here are the steps you can follow：

1. In Power Query -- Add Column – Index Column – From 1.

2. Copy Table to Table2.

3. Select [City] – Transform – Split column:

4. Select [City.1] – [City.6] – Transform – Unpivot Columns.

5. Remover Column – [Attribute].

6. Select the two columns – Right click – Remove Duplications.

Result:

7. Create calculated column.

Column =
CONCATENATEX(
    FILTER(ALL('Table2'),
    'Table2'[Index]=EARLIER('Table'[Index])),[Value],",")

8. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[arinyc]

In addition to my original post and question here is a GDrive LINK with sample data in PBI format.