Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@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
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.
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.
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
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"
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
User | Count |
---|---|
88 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |