filtering and saving into new column

[dhruvgulati]

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

[dhruvgulati]

over 51 views no help pls contribute

[Vijay_A_Verma]

Some sample data is needed to provide the required solution.

[dhruvgulati]

13m ago

expected output
col1   col2   col3

Hi I am having issue with this product	product	product issue
Error product	issue	product
defective product		product
issue report		issue
I am having triouble

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc1RCoAgDAbgqwyfu0ZQZxAfTFcOKmXOun6kCL6N/9v+aa0WghXsBcE+dB9AOReElySABMqQOPriRE2qT2bSamaOPFi9quJxRyf04KDCsWxn89bPmCL/VLPxf9/9yXw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column2"}),
    BuffList = List.Buffer(List.RemoveNulls(#"Replaced Value"[Column2])),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value", "Result", each Text.Combine(List.Intersect({BuffList,Text.Split([Column1]," ")},Comparer.OrdinalIgnoreCase),", "))
in
    #"Added Custom1"

[dhruvgulati]

@Vijay_A_Verma  hi i want to use it in my current workbook it is giving error
token identifier expected 
if i remove line-------let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),-------
then it works and start generating endless errors 


let
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),

let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column2"}),
BuffList = List.Buffer(List.RemoveNulls(#"Replaced Value"[Column2])),
#"Added Custom1" = Table.AddColumn(#"Replaced Value", "Result", each Text.Combine(List.Intersect({BuffList,Text.Split([Column1]," ")},Comparer.OrdinalIgnoreCase)," "))
in
#"Added Custom1"

[Vijay_A_Verma]

Use this

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column2"}),
    BuffList = List.Buffer(List.RemoveNulls(#"Replaced Value"[Column2])),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value", "Result", each Text.Combine(List.Intersect({BuffList,Text.Split([Column1]," ")},Comparer.OrdinalIgnoreCase)," "))
in
    #"Added Custom1"

[dhruvgulati]

this is running endless col1 has 18k rows and col2 has 3k,

it loaded like 2.9L with over 2.5Lac errors

[Vijay_A_Verma]

Since you have this in Excel, is it possible for you to share your workbook alongwith data and query?

[dhruvgulati]

i am sorry working on company confidential data
i can complete this in java easy peasy but they have restricted me to query have to learn from scratch.
In second solution you removed let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t])
what does this line do?

after giving the desired output it is running endless loop
for big data take any string dump in col

take half dictonary in col 2 and try


i just copy pasted this

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column2"}),
    BuffList = List.Buffer(List.RemoveNulls(#"Replaced Value"[Column2])),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value", "Result", each Text.Combine(List.Intersect({BuffList,Text.Split([Column1]," ")},Comparer.OrdinalIgnoreCase)," "))
in
    #"Added Custom1"

[Vijay_A_Verma · Accepted solution]

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

[dhruvgulati]

col 1 has 18438 col2 has 3567 
while selcting table i have selected these only, they dont have blanks or nulls.

but after showing correct output in query editior when i close and load it doesnt stop running
i just copy pasted the entire code changed table number

[dhruvgulati]

HAPPY 75th India Independence Day to all


It worked it was my mistake i was referencing data on which i already performed another query. 

[dhruvgulati]

Another issue

it is skipping words like which are enclosed with doublequotes 

example

"Demo

Insert/demo

if i try to remove all special character from the column and replace them with space

then words like can't converts into can t then t gets skip

[Vijay_A_Verma]

I need to see few samples for column1 and column2 containing these kind of words...

[dhruvgulati]

[Vijay_A_Verma]

Use this

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    CharsList = {"A".."Z","a".."z"," "},
    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(Text.Select(BuffList1{0},CharsList)," "),a, Comparer.OrdinalIgnoreCase)),", "),i=0], each [i]<BuffList1Count, each [i=[i]+1, x=Text.Combine(List.Select(BuffList2,(a)=>List.Contains(Text.Split(Text.Select(BuffList1{i},CharsList)," "),a, Comparer.OrdinalIgnoreCase)),", ")], each [x]),
    Result = Table.FromColumns(Table.ToColumns(Source)&{GenList},Table.ColumnNames(Source)&{"Result"})
in
    Result

[dhruvgulati]

@Vijay_A_Verma 

[dhruvgulati]

Thank you so much You saved my day if you have free time can you make a youtube video or a comment thread explaing the working