1 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
Result13m 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 |
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"
@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
Since you have this in Excel, is it possible for you to share your workbook alongwith data and query?
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"
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
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
HAPPY 75th India Independence Day to all
It worked it was my mistake i was referencing data on which i already performed another query.
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
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
ResultThank 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
