Count the matching string between tables

Question

Hi Everyone,&nbsp;I am new to PowerBI and hope you can help me with this case 🙂I have table 1Nr123451234567812345672134214512341231341and table 2:Nr1234512345678And Table 3:&nbsp;Nr1234567213A-4/2145I am able to achieve the following Result, while comparing table 1 vs 2 and 3NrFound12345yes12345678yes1234567yes21342145no1234no1231341noMy question is: For the no result, I would like to count the matching string (when more than 4 character are matching): so the end result should look like this:NrFoundMatching12345yes100%12345678yes100%1234567yes100%21342145nopartly matching1234no0 %1231341no0 %Please let me know if that can be achieveable.&nbsp;Thank you so mi,

anonymous · Accepted Answer

Hi,&nbsp;NewbiePBIX88&nbsp;
Based on the data you provided, I used the following example data:

Use the following DAX expression to determine whether the string in Table1 is contained in Table2 or Table3:
Found = 
IF(
    OR(
        CONTAINS(Table2, Table2[Nr], Table1[Nr]),
        CONTAINS(Table3, Table3[Nr], Table1[Nr])
    ),
    "yes",
    "no"
)

Use the following DAX to determine if there is a 100% match:
Column = 
VAR _table2= SELECTCOLUMNS('Table2','Table2'[Nr])
VAR _table3 = SELECTCOLUMNS('Table3','Table3'[Nr])
RETURN
IF(
    'Table1'[Nr] IN _table2 || 'Table1'[Nr] IN _table3,"100%", IF(
        OR(
        CONTAINS(Table2, Table2[Nr], Table1[Nr]),
        CONTAINS(Table3, Table3[Nr], Table1[Nr])
    ),"Partly","0%"
    )
)

So there's a problem, as you described, when there are more than 4 character matches, a partial match is displayed, otherwise 0% is displayed.
Take the example you provided, for example, there is a 21342145 in Table, and there is 213A-4/2145 in Table3. The two strings should show a partial match.
In Power BI, each cell is treated as a finished string, and it's hard to exhaustively enumerate each character in another column.
Using the above two strings as an example, power BI treats the 21342145 as a whole and searches for them at 213A-4/2145. But 213A-4/2145 strings, DAX or PowerQuery are difficult to implement, or you need to split each character into separate columns and then match each of these columns.
It's going to be a complicated process. On the contrary, this requirement can be easily implemented in Python. In Python I used the pandas library and then the Python connector in Power BI desktop:

&nbsp;
&nbsp;
Use the following Python script:
import pandas as pd

# Create data tables
table1 = pd.DataFrame({'Nr': ['12345', '12345678', '1234567', '21342145', '1234', '1231341']})
table2 = pd.DataFrame({'Nr': ['12345', '12345678']})
table3 = pd.DataFrame({'Nr': ['1234567', '213A-4/2145']})

# Define matching function
def match_percentage(nr, table):
    for t_nr in table['Nr']:
        # Check if more than 4 characters match
        if len(set(nr) &amp; set(t_nr)) &gt; 4:
            return "partly matching"
    return "0 %"

# Calculate matching results
results = []
for nr in table1['Nr']:
    if nr in table2['Nr'].values or nr in table3['Nr'].values:
        results.append({'Nr': nr, 'Found': 'yes', 'Matching': '100%'})
    else:
        matching = match_percentage(nr, table2) if match_percentage(nr, table2) == "partly matching" else match_percentage(nr, table3)
        results.append({'Nr': nr, 'Found': 'no', 'Matching': matching})

# Convert to DataFrame
result_df = pd.DataFrame(results)

print(result_df)

Here are the results:

&nbsp;
&nbsp;

The above is a feasible idea or a method that can be implemented at the moment.&nbsp;I've provided the PBIX file used this time below.
&nbsp;
&nbsp;
&nbsp;
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;