Forum Discussion
filtering and saving into new column
I have 2 column 1st. has strings 2nd column is unique set of words.
col1 has 2lac entries
col2 has 10000 words
3rd col is blank
i want if (col1 row1)contains any word from col2 -----concate that word in col3 with space delimiter (result should be in saw row as col1)
similarly for col1 row2,col1 row3...till end
I am a java devloper how do we nest loops in power query?
i am new to power query and have been restricted only to excel
your help would be really appreciated
thanks
You will need to remove complete Source line in my code and replace that with your source line. _t are generated when you use PQ's Enter Data feature to generate data rather than pulling from a source.
Use the below new code for your task
let Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content], BuffList1 = List.Buffer(Source[Column1]), BuffList2 = List.Buffer(List.RemoveNulls(List.ReplaceValue(Source[Column2],"",null,Replacer.ReplaceValue))), BuffList1Count = List.Count(BuffList1), GenList = List.Generate(()=>[x=Text.Combine(List.Select(BuffList2,(a)=>List.Contains(Text.Split(BuffList1{0}," "),a, Comparer.OrdinalIgnoreCase)),", "),i=0], each [i]<BuffList1Count, each [i=[i]+1, x=Text.Combine(List.Select(BuffList2,(a)=>List.Contains(Text.Split(BuffList1{i}," "),a, Comparer.OrdinalIgnoreCase)),", ")], each [x]), Result = Table.FromColumns(Table.ToColumns(Source)&{GenList},Table.ColumnNames(Source)&{"Result"}) in Result
18 Replies
Some sample data is needed to provide the required solution.