Looking for a close match based on a list of other names , when a company has different names

[Pandadev]

Hi I am trying to find a way to match two tables from seperate sources. to check the company name is matched. The issue I have is that some of the companies have more than name associated to it. Like registered name , also known as , doing business as.

An example where I am trying to find a match is.

Table 1 which is from an external source. Has a company name called Bob's Burgers Pty linked to registration number 002AP 

Table 2 has the following company names linked to registration number 002AP

BB Food

Bob Smith

Bobs Burgers

Is there a way I can use some kind of fuzzy logic to compare all matched names , to identify that it is a close match.

[amitchandak]

@Pandadev , refer if these can help

https://www.poweredsolutions.co/2019/03/26/fuzzy-matching-in-power-bi-power-query/
https://powerbi.microsoft.com/en-us/blog/tag/fuzzy-matching/
https://www.youtube.com/watch?v=I65YCrJWliw

[Mariusz]

Hi @Pandadev 

If you have registration number 002AP that is a one to one match, why not just use that, else you can use fuzzy matching in the query editor on all three options and hope one will match but it will be a rather slow query, you could also clean text from all the extra symbols like apostrophes split text to individual words in both tables and compare list if they match.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Pandadev]

I need to check that matched registration in table 2 has the correct company name . As with the example not all names are an exact match , and table 2 stores more than 1 company name on some occaisons , I need to check all of the names associated to the reg , to see if there is a close match in any of the names. so in the example it would identify a close match based on Bobs Burgers. would maybe good aswell if possible to ignore any characters , like .,-_ if possible.

[Mariusz]

Hi @Pandadev 

Can you create samples for both tables with a few examples that can be used to develop a solution?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Pandadev]

Table 1 

Reg NoCompany Name

A101	BOB'S BURGERS PTY
A102	DAVES DOGS HOME PTY.
A103	STEVES SOCCER SCHOOL LTD.
A104	NIGELS NEWSPAPERS

Table 2

Reg NoCompany nameTypematch

A101	BOB SMITH	Owner	N
A101	BB FOOD	Registered name	N
A101	BOBS BURGERS	DBA	Y
A102	DAVE JONES	Owner	N
A102	DJ DOGS	Registered name	Y
A102	DAVES DOGS HOME	DBA	Y
A103	ROB JONES	Owner	N
A103	RJ ROUNDERS	DBA	N
A104	NIGEL SMITH	Owner	N
A104	NS NEWSPAPERS	Registered name	Y

So in this example A101 , A102 , A104 , had been identified as a good match

A103 - no match

[Mariusz]

Hi @Pandadev 

You can add the below custom column to your table2, also please see the attached file for ref.

let
    Source = Table2,
    #"Added Custom" = Table.AddColumn( Source, "Custom", each 
        let 
            selectCompanyNamesFromTable1 = Table.SelectRows( Table1, (a)=> [Reg No] = a[Reg No] )[Company Name],
            table1CompanyNameSplit = Text.Split( selectCompanyNamesFromTable1{0}, " " ),
            table2CompanyNameSplit = Text.Split( [Company name], " " )
        in 
            List.ContainsAny( table2CompanyNameSplit,table1CompanyNameSplit )
    )
in 
    #"Added Custom"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Pandadev]

Thanks , would it be possible to add the conditional column to table 1 , and add the first match only , and if no match leave blank , so as I need the row structure in table 1 kept the same , no duplicate rows. Plus if there is no reg no match then leave as blank