Forum Discussion
filtering and saving into new column
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
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 |
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"
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
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"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"this is running endless col1 has 18k rows and col2 has 3k,
it loaded like 2.9L with over 2.5Lac errors