cancel
Showing results 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?

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors