[SOLVED] DAX LOOKUPVALUE on text/string

Issue

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

Solution

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:

1. Source: a reference to Table1
2. Added Column Key lists: adds a custom column with lists of the Table2[Key] value(s) that are in the [String] value. This is the logic for this Custom column:

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.

3. Expanded Key list: expands the lists in the [Key] column
4. Join with Table2 on Key: Joins with Table2 on the Key Value
5. Expanded Table2: Expands the table values in the [ItemTables] column and keeps the [Item] column
6. Group and concate keys/items: Groups the Output table on String, concatenating the Keys and the Items. If you don’t want to see the [Key] column, delete {"Key", each Text.Combine([Key], " | "), type text}, from this step

The 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

Answered By – Marco Vos

Answer Checked By – Katrina (BugsFixing Volunteer)