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.

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.

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.

2 ACCEPTED SOLUTIONS
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))))

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

Now, the final step displays as desired.

3 REPLIES 3
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).

Now, the final step displays as desired.

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

Frequent Visitor

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?

Helpful resources

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors