Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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