Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Pandadev
Post Prodigy
Post Prodigy

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

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.

7 REPLIES 7
Mariusz
Community Champion
Community Champion

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


 

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
Community Champion
Community Champion

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

 

Table 1 

Reg NoCompany Name

A101BOB'S BURGERS PTY
A102DAVES DOGS HOME PTY.
A103STEVES SOCCER SCHOOL LTD.
A104

NIGELS NEWSPAPERS

 

Table 2

Reg NoCompany nameTypematch

A101BOB SMITH OwnerN
A101BB FOODRegistered nameN
A101BOBS BURGERSDBAY
A102DAVE JONESOwnerN
A102DJ DOGSRegistered nameY
A102DAVES DOGS HOMEDBAY
A103ROB JONESOwnerN
A103RJ ROUNDERSDBAN
A104NIGEL SMITHOwnerN
A104NS NEWSPAPERSRegistered nameY

 

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

A103 - no match

Mariusz
Community Champion
Community Champion

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

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors