Assign same value for duplicate entry

[Anonymous]

Hi,

I have a table which has a columns that contains duplicate entries. But there is another column where the values are different.  I want to remove the unwanted values and assign the desired values.

In following example; there are some parts with duplicate entries in 'Part' column. But 'Color' column values are different. I want to have a column ('RESULT') where I can show only the valid vaues if the parts are same.

Part	Color	RESULT
Part1	Red	Red
Part1	XYZ	Red
Part2	Blue	Blue
Part3	ABC	NA
Part3		NA
Part4		NA

Thanks

[az38 · Accepted solution]

Hi @Anonymous 

First, create a dimension table, lets it be named "dimColours". It should be a one column table 

You could either import it or create manually using Enter Data function:

Color

Red

Blue

White

Then add in your initial table a column

RESULT = 
var _colorPart = FIRSTNONBLANK(SELECTCOLUMNS(FILTER(ALL('Table');'Table'[Part]=EARLIER([Part]) && 'Table'[Color] IN VALUES(ColorTable[Color]));"Color";'Table'[Color]);1)
return
IF(ISBLANK(_colorPart);"NA";_colorPart)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[butlerjay87]

Add a new table (effectively a LOOKUP table).

Then add the correct colours to the parts.

You just need to then set up a 1:Many relationship between the 2 tables on the 'Parts' column.

Please accept solution if this works for you.