Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mareno_123
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

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
JarroVGIT
Resident Rockstar
Resident Rockstar

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!




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

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!




thank you so so much

its work for me

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.