Match value after fuzzy lookupvalue between two tables

[QuintonLi]

Hi all,

I need some help on match/LookUpValue in two different tables about streets and suburb, where the first table contants streets of all incidents, and the second table contants all the possible streets in suburb. I would like to do a match/lookup to give the complete street name and suburb in table 1 based on informaiton in table 2 (I can do this in Excel using  fuzzy lookup by set up StreetName & "*" then the lookup function could will covers street with the key word)

I tried several different method in PowerBI, but still not sucessful to get the output I want, please help if you would be able to. Thanks!

Table 1: 

STREET                     SUBURB                        STREETLOOKUP

MAIN                        BURLINGTON               BURLINGTON*MAIN

MAIN                        GREENSBORO              GREENSBORO*MAIN

MAIN AV                  GREENSBORO              GREENSBORO*MAIN AV

SECOND                  BURLINGTON                BURLINGTON*SECOND

SECOND ST              BURLINGTON                BURLINGTON*SECOND ST

SECOND PL              GREENSBORO              GREENSBORO*SECOND PL

Table 2:

STREET                     SUBURB                      LookupStreet

MAIN STREET          BURLINGTON               BURLINGTON*MAIN STREET             

MAIN AVENUE         GREENSBORO             GREENSBORO*MAIN AVENUE          

SECOND STREET      BURLINGTON              BURLINGTON*SECOND STREET          

SECOND PLACE        GREENSBORO             GREENSBORO*SECOND PLACE

AND I want to do a lookup/match so Table 1 could show the correct complete street name under each suburb as following: 

STREET            SUBURB                   STREETLOOKUP                         FinalResult

MAIN             BURLINGTON           BURLINGTON*MAIN                  BURLINGTON*MAIN STREET

MAIN              GREENSBORO         GREENSBORO*MAIN                 GREENSBORO*MAIN AVENUE

MAIN AV         GREENSBORO         GREENSBORO*MAIN AV           GREENSBORO*MAIN AVENUE

SECOND          BURLINGTON          BURLINGTON*SECOND             BURLINGTON*SECOND STREET

SECOND ST     BURLINGTON          BURLINGTON*SECOND ST        BURLINGTON*SECOND STREET

SECOND PL     GREENSBORO         GREENSBORO*SECOND PL        GREENSBORO*SECOND PLACE

[v-juanli-msft · Accepted solution]

Hi @QuintonLi 

As tested, 

In Edit queries, trim and clean columns "STREET" and "SUBURB' in both tables, then add merged columns in both tables

Close&&apply, create a new table

Lookup Table =
FILTER (
    CROSSJOIN ( VALUES ( Table1[lookup value] ), VALUES ( Table2[location1] ) ),
    LEFT ( [location1], LEN ( [lookup value] ) ) = [lookup value]
)

Then create a calculated column in Table1

final =
LOOKUPVALUE (
    'Lookup Table'[location1],
    'Lookup Table'[lookup value], Table1[lookup value]
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @QuintonLi 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @QuintonLi 

Make a test as parry2k suggested, if there is still any problem, feel free to let me know.

Best Regards
Maggie

[parry2k]

@QuintonLi check fuzzy loopup blog here, hope it helps.