Searching for a partial match in a string based on values in a another table

[tkcsp]

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!

[Icey · Accepted solution]

Hi @tkcsp ,

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.

[mwegener]

Hi @tkcsp ,

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.

[tkcsp]

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?