Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
arinyc
Helper I
Helper I

Filter out unique values from the cell that contains multiple values

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.

List.PNG

 

 

 

 

 

 

 

 

 

Best,

1 ACCEPTED SOLUTION
lazarjo
Advocate II
Advocate II

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(_))),",")

 

 

image.png

 

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

 

Capture.PNG

 

Best

View solution in original post

4 REPLIES 4
lazarjo
Advocate II
Advocate II

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(_))),",")

 

 

image.png

 

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

 

Capture.PNG

 

Best

Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yangliu-msft
Community Support
Community Support

Hi  @arinyc ,

 

Here are the steps you can follow:

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

vyangliumsft_0-1681957772715.png

2. Copy Table to Table2.

vyangliumsft_1-1681957772718.png

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

vyangliumsft_2-1681957797514.png

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

vyangliumsft_3-1681957797518.png5. Remover Column – [Attribute].

vyangliumsft_6-1681957846041.png

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

vyangliumsft_5-1681957821511.png

Result:

vyangliumsft_7-1681957857438.png

7. Create calculated column.

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

8. Result:

vyangliumsft_8-1681957857439.png

 

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
Helper I
Helper I

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.