cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Match value after fuzzy lookupvalue between two tables

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Match value after fuzzy lookupvalue between two tables

Hi @QuintonLi 

As tested, 

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

Capture9.JPG

 

Close&&apply, create a new table

Capture11.JPG

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

Capture12.JPG

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.

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Match value after fuzzy lookupvalue between two tables

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Community Support
Community Support

Re: Match value after fuzzy lookupvalue between two tables

Hi @QuintonLi 

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

 

Best Regards
Maggie

Highlighted
Community Support
Community Support

Re: Match value after fuzzy lookupvalue between two tables

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
Highlighted
Community Support
Community Support

Re: Match value after fuzzy lookupvalue between two tables

Hi @QuintonLi 

As tested, 

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

Capture9.JPG

 

Close&&apply, create a new table

Capture11.JPG

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

Capture12.JPG

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.

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors