CODEWITHSUNDEEP
powerbidaxpowerquerym
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

Power BI join two tables on LIKE clause

How to join two tables in Power BI on LIKE clause? Suppose we have two tables:

+------------------+  +--------+
|    Messy_Name    |  |  Tag   |
+------------------+  +--------+
| red apple        |  | apple  |
| apple very tasty |  | banana |
| good apple green |  +--------+
| yellow banana    |            
| banana split     |            
+------------------+

And we want to join them in PBI M or DAX (preferably both) imitating this SQL query:

select
  a.Messy_Name
 ,b.Tag
from FactTable a
outer apply
    (
    select top 1
     b.Tag
    from TagList b
    where a.Messy_Name like '%'+b.Tag+'%'
    order by b.Tag
    ) b

So desired results would be:

+------------------+--------+
|    Messy_Name    |  Tag   |
+------------------+--------+
| red apple        | apple  |
| apple very tasty | apple  |
| good apple green | apple  |
| yellow banana    | banana |
| banana split     | banana |
+------------------+--------+

So the query is supposed to return the first Tag from the TagList table where Messy_Name contains the Tag. Eventually, this is one to one relationship.

Upvotes: 3

Views: 4711

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40264

To do this in Power Query, create a custom column with this formula

List.Max(
    Table.SelectRows(Tags,
       (T) => Text.Contains([Messy_Name], T[Tag]))[Tag])

Upvotes: 7

Alexis Olson
Alexis Olson

Reputation: 40264

In DAX, you can do similar logic to the SQL by taking the top Tag that is contained in the Messy_Name. Here's the formula for the calculated column:

Tag = CALCULATE(
          MAX(Tags[Tag]), 
          FILTER(Tags,
              SEARCH(Tags[Tag],
                  Messy[Messy_Name],1,0
              ) > 0
          )
      )

Upvotes: 3

Related Questions