Clean duplicate words in a string using CONCATENATEX funciton

[aramirez2]

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
A	A1	green
A	A2	green
A	A3	blue
B	B1	green
B	B2	red

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.

[aramirez2]

Hi @amitchandak  Thanks for your answer.

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

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

Thanks for your help.

Regards.

[amitchandak]

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

[amitchandak]

@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

[aramirez2]

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:

ID	Label
A	green-blue
B	green-red

Thanks for your time

[smpa01 · Accepted solution]

@aramirez2  you can use a measure like this

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

If you need a calculated column

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

[aramirez2]

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).