new table of unique values in power pivot not in power bi

[Mareno_123]

hi,

can you help mi with this ?

i have table with 2 columns (ID, colour)

ID , colour

1, black

1, red

1, green

2, black

2, red

3, black

4, red

5, white

5, green

and i need new table with only one colour and unique ID like this:

ID, colour

1, green

5, green

thank you so much

[JarroVGIT]

You need to be more specific. Does it has to be green? Are you looking for a PowerQuery solution or a DAX solution? Does it needs to be a filtered table or a new calculated table? 

What are you actually trying to achieve? We are missing a whole lot information. If you can't provide more information, then going on what you described you can filter the Color column in the PowerQuery Editor and select only 1 column, click Save & Apply and then your table only contains that color.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Kudo's are welcome 🙂

[Mareno_123]

Thank you for your response.
Originally, I wanted a new table where only the IDs that contained the color would remain based on the color you selected (green in the example).
But I would need to mark all IDs that contained the color as follows:

ID , colour , new column

1, black , green

1, red, green

1, green, green

2, black, no green

2, red, no green

3, black, no green

4, red, no green

5, white, green

5, green, green

i know simple mark green with:

=IF([colour]="green";"green";"no green") , this work for 1, green and 5, green. But i need mark with "green" another rows :1, black and 1, red, and 5 white.

Thanks

[JarroVGIT · Accepted solution]

The following should work (typing on a phone, apologies for typos).
Calculated column = IF ( CONTAINS ( FILTER(ALL(Table), [id] = EARLIER([id])), [colour], "green"), "green", "no green")

[Mareno_123]

thank you so so much

its work for me

you are great and you save few years of my life 🙂