Forum Discussion

Jenni-Sky's avatar
Jenni-Sky
Icon for Helper I rankHelper I
6 years ago
Solved

Compare Two Columns and Check for Partial Match

Hi,    I have a table of data which includes a "Customer Name" column and an "Invoice Customer" column.    I am looking to create a "Match" column, to check whether Customer Name and Invoice Name...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Jenni-Sky 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a calculated column as below.

    Match = 
var _cname = 'Table'[Customer Name]
var _iname = 'Table'[Invoice Customer]
var _firstname = LEFT(_cname,SEARCH(" ",_cname)-1)
var _lastname = MID(_cname,SEARCH(" ",_cname)+1,LEN(_cname))
return
IF(
    EXACT([Customer Name],[Invoice Customer]),
    "Match",
    IF(
           NOT(CONTAINSSTRING(_iname,_firstname))&&NOT(CONTAINSSTRING(_iname,_lastname)),
           "Not Match",
           IF(
               CONTAINSSTRING(_iname,_firstname)||CONTAINSSTRING(_iname,_lastname),
               "Partial Match"
           )
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Jenni-Sky's avatar
Jenni-Sky
Icon for Helper I rankHelper I
6 years ago

Thank you for this amitchandak , I have already viewed this resource, however it relates to joining two different tables. My data is already in the same table and I only want to compare within the row. I was struggling to see how to apply the resource to my situation. 

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Jenni-Sky , Try like. You might need to do few changes to 2nd condition. A new column 

Switch ( True(),
containsstring([Customer Name],[Invoice Customer]) , "Match"
SEARCH(left([Customer Name],SEARCH(" ",[Customer Name],1,0)-1) ,[Invoice Customer],1,0)>0
|| SEARCH(left([Invoice Customer],SEARCH(" ",[Invoice Customer],1,0)-1) ,[Customer Name],1,0) >0 ,"Partial Match",
"No Match")