Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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
First 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.