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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
quadma
Frequent Visitor

Find text string in column and use unique values to lookup value in a corresponding column

I don't know exactly how to describe what I'm trying to do, so hopefully the below pictures will help.  I would like to create a list of all the unique values (which are separated by commas) in column B of the grey table and then use that to create a new table (the blue table) that lists the favorite colors and then in column F, lists all the people  for whom that is a favorite color.   

 

goal.png

I got as far as creating a list of the unique values from column B, but am absolutely stumped on my next step.  I've tried multiple things but none have been successful so far. 

Unique color values.png

2 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User
Super User

NewStep= #table({"Color","People"},Record.ToList(List.Accumulate(Table.ToRows(PreviousStepName),[],(x,y)=>Record.TransformFields(x,List.Transform(Text.Split(y{1},","),(x)=>{x,each {_{0}? ??x,Text.Combine({_{1}?,y{0}},",")}}),2))))

View solution in original post

quadma
Frequent Visitor

I figured out a work around.  Prior to wdx223_Daniel's new step, I added a step by using the transform function to replace each instance of ", " (comma and a space) with "," (just a comma). 

quadma_4-1657067376627.png

Now, the final step displays as desired.

 

View solution in original post

3 REPLIES 3
quadma
Frequent Visitor

I figured out a work around.  Prior to wdx223_Daniel's new step, I added a step by using the transform function to replace each instance of ", " (comma and a space) with "," (just a comma). 

quadma_4-1657067376627.png

Now, the final step displays as desired.

 

wdx223_Daniel
Super User
Super User

NewStep= #table({"Color","People"},Record.ToList(List.Accumulate(Table.ToRows(PreviousStepName),[],(x,y)=>Record.TransformFields(x,List.Transform(Text.Split(y{1},","),(x)=>{x,each {_{0}? ??x,Text.Combine({_{1}?,y{0}},",")}}),2))))

Thanks for the idea!  That almost got me where I wanted to be, but doesn't remove the duplicates of colors that more than one person likes.  I need to, for example, get Dante with Jane and Bo in the first row as they all like blue. I'm guessing it's because there is a space in front of some of the colors, so I need to trim that off so that the cells are actually the same.  I know how to do that as a final step, after the step you gave me, but am not sure how to incorporate it into your step.  Any ideas?

test.png

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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

Top Solution Authors