Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |