Fuzzy lookup match

[pbiforum123]

Table:1

Campaign Objective Campaign Code
Awareness AWA
Consideration CONSI
Video views VID
Store Traffic STORE
Reach REACH

Table:2

Campain Objective Code Output
AWA Awareness
AWA TraffA Awareness
CONSI O-2LH7G Consideration
AWA O-2VL9K Awareness
VID Video views
STORE Store Traffic
C experiment C experiment
NA NA
Awareness Awareness
Reach (1+) Reach
Followers Followers

If the value of column "Campaign Code" in table 1 exists in sub string of column "Campain Objective Code" in table 2, then do the lookup and get the mappping value based on the match from "Campaign Objective" column from Table 1 and put it in Output column of Table:2 as you see above. If the match is not found put the value as it is for example "C experiment" and "Followers"

Can someone please help on this?

[v-yiruan-msft]

Hi @pbiforum123 ,

You can create a calculated column as below to get it, please find the details in the attachment.

Output = 
VAR _cobj =
    CALCULATE (
        MAX ( 'Table1'[Campaign Objective] ),
        FILTER (
            ALL ( 'Table1' ),
            IFERROR (
                SEARCH ( 'Table1'[Campaign Code], 'Table2'[Campaign Objective Code], 1, 0 ),
                0
            ) > 0
        )
    )
RETURN
    IF ( ISBLANK ( _cobj ), 'Table2'[Campaign Objective Code], _cobj )

Best Regards