Forum Discussion
Find a Word in a Text Column based on another Table
Hi,
I'm trying to find a Word in a Column based on a Column from another Table.
So, my Table A is like:
| Values to be searched |
| Chair Toro |
| Chair Tammy |
| Chair Dayton |
| Chair Thor |
| Office chair Thor |
| Office chair Toro |
Office chair Dayton |
My Table B how contains a List of Names locks like this:
| Value to search for | Value to search for |
| Dayton | Dayton |
| Tammy | Tammy |
| Vancouver | Vancouver |
| Thor | Thor |
And so on.
Now I try to find for Example the Word Tammy in Table A using this custom coulum:
let myvalue=[Values to be searched]
in
Text.Combine(
Table.SelectRows(TableB,
each Text.Contains(myvalue,[Value to search for]))[Corresponding value]
,
",")
The Result I am getting is the following:
| Values to be searched | Result |
| Chair Toro | Tor,st,ro,,To |
| Chair Tammy | am,Tam,le,Ta,tl,stl,Tamm,,Tammy |
| Chair Dayton | Dayton,ro,,Da,Day |
| Chair Thor | ho,ro,,Thor |
| Office chair Thor | ho,ro,,Thor |
| Office chair Toro | Tor,st,ro,,To |
Office chair Dayton | Dayton,ro,,Da,Day |
So, the problem is, my way is not only locking for the entire word, but also for parts of it and therefore even if the world is not in Table B I still get some findings.
My question is, is there a way to make sure that my formula is only locking at the entire word and not just part of it?
Code for Table A - 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("i45Wcs5IzCxSCMkvylcISczNrVQIycgvUorVgcuABJH4LomVJfl5yApg6v3T0jKTUxWS8YkCrcEQPbQAZmYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Values to be searched" = _t]), BuffList = List.Buffer(#"Table B"[Value to search for]), #"Added Custom" = Table.AddColumn(Source, "Result", each Text.Combine(List.RemoveNulls(List.Transform(Text.Split([Values to be searched]," "), (x)=>if List.Contains(BuffList,x) then x else null)),", ")) in #"Added Custom"Test code for Table B, if you need
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckmsLMnPU4rViVYKSczNrQSzwhLzkvNLy1KLIOIZ+UBGLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Value to search for" = _t]) in Source
3 Replies
Code for Table A - 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("i45Wcs5IzCxSCMkvylcISczNrVQIycgvUorVgcuABJH4LomVJfl5yApg6v3T0jKTUxWS8YkCrcEQPbQAZmYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Values to be searched" = _t]), BuffList = List.Buffer(#"Table B"[Value to search for]), #"Added Custom" = Table.AddColumn(Source, "Result", each Text.Combine(List.RemoveNulls(List.Transform(Text.Split([Values to be searched]," "), (x)=>if List.Contains(BuffList,x) then x else null)),", ")) in #"Added Custom"Test code for Table B, if you need
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckmsLMnPU4rViVYKSczNrQSzwhLzkvNLy1KLIOIZ+UBGLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Value to search for" = _t]) in SourceFirst of all, Thank you for your Soulotion.
Unfontiontly its not working for my complet Dataset.
When aplying your Soclution.
I get the Error: Expression.Error: We cannot convert a value of type List to type Table.
My M-Code is:Where only the last two comands are important.
#"Umbenannte Spalten2" = Table.RenameColumns(#"Entfernte Spalten2",{{"Bezeichnung", "Values to be searched"}}),
BuffList = List.Buffer(#"Table B"[Value to search for]),
#"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(BuffList, "Benutzerdefiniert", each Text.Combine(List.RemoveNulls(List.Transform(Text.Split([Values to be searched]," "), (x)=>if List.Contains(BuffList,x) then x else null)),", "))
in
#"Hinzugefügte benutzerdefinierte Spalte1"Replace this portion Table.AddColumn(BuffList
with
Table.AddColumn(#"Umbenannte Spalten2"
Basically, this step has to refer to previous step which refers to a table. When you put Bufflist, it referred to a list not to a table.
