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
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.
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 ResultAnother 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
I need to see few samples for column1 and column2 containing these kind of words...