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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
aramirez2
Helper I
Helper I

Clean duplicate words in a string using CONCATENATEX funciton

Hi.

 

I have a issue trying to concatenate strings.

 

My model contains 2 tables: Product and Component. Each product is built up by 2 or more components. Each component contains a label.

 

For instant, Product "A" is created by assembling "A1", "A2" and "A3" components which have "green", "green" and "blue" labels. Therefore Component table has the following structure:

 

PRODUCT_id  COMPONENT_id  LABEL
AA1green
AA2green
AA3blue
BB1green
BB2red

 

The result for A product in Product table is "green-green-blue" using the following formula:

 

CONCATENATEX(FILTER(ALL(Component); 'Product'[id] = Component[PRODUCT_id]); Component[label]; "-")

 

My goal is to obtain "green-blue" for Product A in Product table.

 

Does anyone how I can avoid duplicated words in a single labels?

 

Thanks in advance.

1 ACCEPTED SOLUTION

@aramirez2  you can use a measure like this

 

Measure =
CONCATENATEX (
    VALUES ( Component[LABEL] ),
    Component[LABEL],
    ",",
    Component[LABEL], ASC
)

 

 

smpa01_0-1640787613620.png

 

If you need a calculated column

Column =
CONCATENATEX (
    SUMMARIZE ( RELATEDTABLE ( Component ), Component[LABEL] ),
    CALCULATE ( MAX ( Component[LABEL] ) ),
    ",",
    Component[LABEL]
)

 

smpa01_1-1640787892082.png

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

6 REPLIES 6
aramirez2
Helper I
Helper I

Hi @amitchandak  Thanks for your answer.

 

However your measure formula does not run due to the different component labels for a single Product:

 

aramirez2_1-1640776477672.png

 

Would it be possible to create a columns instead of a measure?

 

Thanks for your help.

 

Regards.

@aramirez2 , do you have two tables? If yes, share sample data for both and sample output in table format

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
amitchandak
Super User
Super User

@aramirez2 , Try a measure like

Measure = CONCATENATEX(summarize(FILTER('Product', 'Product'[PRODUCT_id] = max('Product'[PRODUCT_id])),'Product'[  LABEL]), 'Product'[  LABEL], "-")

 

Assumed product as table na,e

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thanks @amitchandak 

 

Your formula is running now but the result is not as expected. There are still duplicated words as at the beginning: "green-green-blue".

 

My model contains two tables:

 

- Table Product with only ID column. Furthermore I want a new column "Label" which should concatenate all their components labels.

- Table Component with 3 columns; PRODUCT_id, COMPONENT_id and LABEL (as my previous table example)

 

So expected Product table result is:

 

IDLabel
Agreen-blue
Bgreen-red

 

Thanks for your time

@aramirez2  you can use a measure like this

 

Measure =
CONCATENATEX (
    VALUES ( Component[LABEL] ),
    Component[LABEL],
    ",",
    Component[LABEL], ASC
)

 

 

smpa01_0-1640787613620.png

 

If you need a calculated column

Column =
CONCATENATEX (
    SUMMARIZE ( RELATEDTABLE ( Component ), Component[LABEL] ),
    CALCULATE ( MAX ( Component[LABEL] ) ),
    ",",
    Component[LABEL]
)

 

smpa01_1-1640787892082.png

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Thanks a million! @smpa01 

 

I avoided duplicates with your solution. I only had to connect both tables to make your column and measure work (they were not connected previously).

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!