cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Highlighted
Super User II
Super User II

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 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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

Highlighted

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted

thank you so so much

its work for me

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

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors