Mapping columns from different tables where one has multiple delimited values

[user180618]

I have two tables that I would like to map together, either using a join or relationship or a lookup (not sure which is most appropriate). In Table 1 I have a column that lists English words/slang and the different ways they are said across US, UK and Australian English separated by commas:

Column1.words

cigarettes, cigs, butts, f*gs, durry

tracksuit bottoms, tracksuits, sweatpants, trackies, dacks

In Table 2, I have the categorisations of the words by US, UK and Australian:

US_english	UK_english	AU_english
cigs	f*gs	durry
candy	sweets	lollies
sweatpants	trackies	trackies

I want to have a Column2 in Table 1 that pulls out a word from the list Column1 list (cigarettes, cigs...) based on the US_english table column in Table 2, so that my Table 1 then has two columns like this:

Column1.words	Column2.matched
cigarettes, cigs, butts, durry	cigs
tracksuit bottoms, tracksuits, sweatpants, trackies, dacks	sweatpants

What would be the best way to do this?

[tamerj1 · Accepted solution]

Hi @user180618 
Please try

Matched =
MAXX (
    FILTER (
        VALUES ( Table2[US_english] ),
        CONTAINSSTRING ( Table1[Word], Table2[US_english] )
    ),
    Table2[US_english]
)

The MAXX shall not be required and can be removed

[ACS_BIM]

I tried this and this is the error I received:

[tamerj1]

@ACS_BIM 

this formula is to be used to create a calculated column. To use it in a query you need to use ADDCOLUMNS 

[user180618]

I'm getting this error: Expression.Error: The name 'MAXX' wasn't recognized. Make sure it's spelled correctly.

When I remove MAXX I get a 'Token RightParen expected' error on the comma in the third-last line.

[tamerj1]

@user180618 

Would you post a screenshot of the DAX code that you have used?

[user180618]

Never mind, it worked now. I was doing it in Power Query not Power BI.

Thanks very much!