Forum Discussion
Find text string in column and use unique values to lookup value in a corresponding column
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))))
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.
Community ChampionNewStep= #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?