Compare Two Columns and Check for Partial Match

[Jenni-Sky]

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 are a match, or partial match, or no match. 

This is what I would like to achieve:

Customer Name	Invoice Customer	Match
Elephant Insurance	Elephant Insurance	Match
Paper Makers	Green Printers	No Match
3D Mouse	3dmouse	Partial Match
Random University	Random Uni	Partial Match
Hill University	University of Hill	Partial Match

I am looking for a way to compare the two columns to see whether there is a match or partial match. 

I appreciate it is unlikely to always recognise the partial matches, but it would be very helpful if there were a way to identify some of the similar values. 

Thanks in advance! 

[v-alq-msft · Accepted solution]

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.

[MFelix]

Hi @Jenni-Sky 

I have answered to a similar question on the post below:

https://community.powerbi.com/t5/Desktop/Matching-and-returning-a-list-of-all-matching-words-within-...

this answer was based on the blog post below:

https://radacad.com/quick-dax-word-count

https://powerpivotpro.com/2014/01/containsx-finding-if-a-value-in-table-1-has-a-matching-value-in-ta...

Looking at you concrete problem what you can do is the following column:

Output to a Table = 
VAR Sentence = 'FInd'[Customer Name]
VAR SentenceCleaned = " " & Sentence & " "
VAR LengthOfSentence = LEN(SentenceCleaned)
VAR PivotedSentence  = 
    ADDCOLUMNS(
        GENERATESERIES(1,LengthOfSentence) ,
        "Letter" , 
        MID(SentenceCleaned,[Value],1)
    )  
var Boundaries = 
  ADDCOLUMNS(
    PivotedSentence ,
    "PrevSpace", MAXX(FILTER(PivotedSentence ,''[Value] < EARLIER([Value]) && [Letter] = " "),[Value]) + 1,
    "NextSpace", MINX(FILTER(PivotedSentence ,''[Value] > EARLIER([Value]) && [Letter] = " "),[Value]) - 1 
       )
VAR TableOfWords =        
    SELECTCOLUMNS(
        SUMMARIZE(
             FILTER(Boundaries, not [Letter] IN {" "}) ,
            [PrevSpace]
            ),"Word Position",[PrevSpace]
            ) 
    
VAR TableOfWords2 = 
    ADDCOLUMNS(
        TableOfWords,
        "Word",
        CONCATENATEX(
            FILTER(
                Boundaries,[PrevSpace]=[Word Position]),
                [Letter],
                ,
                [Value]
                )
        )
       
RETURN 
iF('FInd'[Customer Name] = 'FInd'[Invoice Customer], "Match",
IF(
      SUMX(TableOfWords2,
           SEARCH(
                upper(TRIM([Word])),
                upper(TRIM('FInd'[Invoice Customer]))
                ,,0
               )
          ) 
> 0,
      "Partial Match",
      "No Match"
     ))

Check the PBIX file attach.

Please be aware that this may need some changes because the data you have given was very reduced but believe can work correctly,is also possible to change ti to a measure however the performance will suffer a lot.

[amitchandak]

@Jenni-Sky , refer if this can help a bit

https://radacad.com/fuzzy-matching-in-power-bi-and-power-query-match-based-on-similarity-threshold

[Jenni-Sky]

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]

@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")

[ToddChitt]

"To a hammer, the whold world is a nail"

I bet you're going to get some "nail" type answers in this Power BI forum because everyone here is a hammer.

But really, Power BI is not the tool for this. Try Fuzzy Matching in SSIS. Or if you have it in a SQL database, maybe you can write some code that will iterate over the two datasets looking for similarities, maybe using regular expressions and the like. 

But really, you're asking hammer and nail people for a solution when you really need an electrician. 🙂