Forum Discussion

QuintonLi's avatar
QuintonLi
Frequent Visitor
6 years ago
Solved

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 poss...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

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.