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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors