Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Hi @QuintonLi
Hi @QuintonLi
Make a test as parry2k suggested, if there is still any problem, feel free to let me know.
Best Regards
Maggie
@QuintonLi check fuzzy loopup blog here, hope it helps.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |