Retrieve Row location from other table based on matches

[Sonaa]

Hi all. Your views or comments are really appreciated.

I have 2 tables. One - "A" table , it has a customer address (short format) and second table "B"- has address(long format). Because of data format issues, I wasn't able to do a like by like match. I developed a methodology to do fuzzy match. So, splitted the customer address column in "A" Table by spaces. So, I got 4 different columns containing the strings. And then I checked whether eg. Customer address.1 was found anywhere in second table's address column. If there was a match, it returned true or else false. Below add1 shows whether the string matched to "B" tabels address column or not.

What I want to achieve?

 

For better clarity of matched address, I want the column which shows that string(in first table- "A") got matched to which row in address column(second table "B").  Basically a row information to help understand the matches address as a whole.

Please help! @Ayushkumar, @Amit @ayush ... All

[tamerj1 · Accepted solution]

@Sonaa 
Please refer to attached sample file with the solution

Best Match Adress = 
VAR CurrentCustAdress = SUBSTITUTE ( TableA[Customer Address - Copy], ", ", " " )
VAR String1 = SUBSTITUTE ( SUBSTITUTE ( CurrentCustAdress, UNICHAR ( 10 ), " " ), ",", " " )
VAR Items1 = SUBSTITUTE ( String1, " ", "|" )
VAR Length1 = COALESCE ( PATHLENGTH ( Items1 ), 1 )
VAR T1 = GENERATESERIES ( 1, Length1, 1 )
VAR T2 = SELECTCOLUMNS ( T1, "@Item2", PATHITEM ( Items1, [Value] ) )
VAR T3 = VALUES ( TableB[Address] )
VAR T4 = 
    ADDCOLUMNS ( 
        T3, 
        "@CountMatch",
        VAR String2 = TableB[Address]
        VAR Items2 = SUBSTITUTE ( String2, " ", "|" )
        VAR Length2 = COALESCE ( PATHLENGTH ( Items2 ), 1 )
        VAR T5 = GENERATESERIES ( 1, Length2, 1 )
        VAR T6 = SELECTCOLUMNS ( T5, "@Item2", PATHITEM ( Items2, [Value] ) )
        VAR T7 = INTERSECT ( T2, T6 )
        RETURN
            COUNTROWS ( T7 )
    )
VAR T8 = TOPN ( 1, T4, [@CountMatch] )
RETURN
    MAXX ( T8, [Address] )

[Sonaa]

Thanks alot tamerj1. That really helps!

[tamerj1]

Hi @Sonaa 

in other words you want to find the best match of table A short address from table B long address. Is that correct? The four columns that you're creating are just steps to obtain the desired result, roght? Can you please provide a dummy sample of both tables?

[Sonaa]

• Hi @tamerj1 .  Thanks for responding!

 Yes, I want to find the best match from table A( short address) to table B(long address). And 4 columns which I have created is in table A by splitting it by space delimeter. Like:- customer address.1 has sauchiel customer address.2 has street... And other columns..

So finding where sauchiel string is existing in table B's Address( full address column). 

I actually got that matching logic sorted using Text.Contain(Text.Combine( B[ Address], [customer address.1]). This gave me True or False , depending on its occurrence. 

Now, I just need to check that when sauchiel string came as true , which Address row from B table did it got matched?

[Sonaa]

 this screenshot is of Table "B" having full address. And below one is of table "A" having short format/vague address, so splitted that into 4 columns by split by delimeter. 

•  Table B. Here add1,2,3,4 and postcode are the matches check, which gave True and False respectively.

Now need to find that if add1 came true, so where did it's customer address.1 matched in table B. 

Your early response would be really helpful as I am stucked on this since. Thanks!

[tamerj1]

Thank you @Sonaa for the explanation.
I can try to find a solution using DAX rather than Power Query. If ok with you please provide a copy/paste sample of data of both tables to work with.

[Sonaa]

Spoiler
Thanks @tamerj1 . Actually I am unable to upload the sample. Can you please try and frame the Dax query ? If so it would be really helpful.

[tamerj1]

@Sonaa 

You don't need to upload a file. If you already have a sample file you can upload to any file transfer service like OneDrive, DropBox or we transfer ans just share the download link. Otherwise, you can copy the tables and paste them as is in a comment or share a clear screenshot (not picture) of both tables. 
Such queries can be a bit complex and the DAX solution is not something that is obtained off-shelf. Sample data os require to create a proper workable solution 

[Sonaa]

 table B.

 table A

•  table B( checking whether match is found)

[Sonaa]

Hi @tamerj1 . Yes, thanks. Have attached the screenshots. Due to some reasons, not able to upload my file here or on OneDrive. Hope this screenshots helps in getting things sorted soon.