Forum Discussion
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.
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))))
3 Replies
- wdx223_Daniel
Community Champion
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?