Forum Discussion
Searching for a partial match in a string based on values in a another table
I'm trying to create a new column based on partial text matches in my first column when compared to my reference table.
My Locations reference table is set up like:
Locations
| City | State | StateAbv |
| augusta | georgia | ga |
| detroit | michigan | mi |
| cleveland | ohio | oh |
The strings would be:
Table1
| Words |
| This is and example string cleveland |
| A Detroit Example |
| augusta value here |
The end result would be:
Table1
| Words | Location |
| This is and example string cleveland | Cleveland |
| A Detroit Example | Detroit |
| augusta value here | Augusta |
What would be the best way to achieve this in DAX or M?
Thanks!
Hi Anonymous ,
Please check if the result below meets your requirements.
Steps:
For details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- mwegener
Most Valuable Professional
Hi Anonymous ,
I would prefer M.
Try a fuzzy matching
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.This solution works for my original question, but I was hoping I'd be able to take the answer and extrapolate on it and I think fuzzy matching presents issues with state abreviations. In the data there will occasionally be references to state names and abreviations and some states share city names (e.g. Columbus, GA and Columbus, OH). Also, I could see an issue with the state Indiana being abreviated as "in" where the phrase "in" will also be used as a preposition.
Updated Locations Table:
City State StateAbv augusta georgia ga detroit michigan mi columbus georgia ga columbus ohio oh evansville indiana in Table1
Words This is an example string for ohio by cleveland an example in detroit augusta ga value here example for cleveland evansville in value Expected end result:
Table1
Words City StateAbv This is an example string for ohio by cleveland cleveland oh an example in detroit detroit augusta ga value here augusta ga example for cleveland cleveland evansville in value evansville in Would fuzzy matching be able to pick up on these nuances?
- Icey
Community Support
Hi Anonymous ,
Please check if the result below meets your requirements.
Steps:
For details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
