Forum Discussion
Assign same value for duplicate entry
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
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
2 Replies
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
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.
