I would like to create some kind of LOOKUPVALUE on text in DAX that will match a sentence with a particular keyword. For instance in the example below the second and third row have a hit because “Apple” and "Chicken" is in the string. The problem is that the text is in a string and not a standalone value.
Table 1
Table 2
Output
EDIT, improved answer: this new version also works when there are multiple keys in one string.
I think PowerQuery is the natural place to perform an operation like this.
The Output table would look like this:
A description of the applied steps:
For each row the function selects the values from the Table2[Key] column that it finds in the [String] value. It then returns a list that holds only the selected values.
{"Key", each Text.Combine([Key], " | "), type text},
from this stepThe script in the Advanced Editor looks like this:
let
Source = #"Table1",
#"Added Column Key lists" = Table.AddColumn(Source, "Key", (r) => List.Select(Table.Column(Table2,"Key"),each Text.Contains(r[String],_,Comparer.OrdinalIgnoreCase)),type text),
#"Expanded Key lists" = Table.ExpandListColumn(#"Added Column Key lists", "Key"),
#"Join with Table2 on Key" = Table.NestedJoin(#"Expanded Key lists", {"Key"}, Table2, {"Key"}, "ItemTables", JoinKind.LeftOuter),
#"Expanded ItemTables" = Table.ExpandTableColumn(#"Join with Table2 on Key", "ItemTables", {"Item"}, {"Item"}),
#"Group and concate keys / items" = Table.Group(#"Expanded ItemTables", {"String"},{{"Key", each Text.Combine([Key], " | "), type text},{"Item", each Text.Combine([Item], " | "), type text}})
in
#"Group and concate keys / items"
Here is a link to my .pbix file