Common/alternate location names?

[dsever04]

Situation: We have a location field where the user is supposed to enter the full city name and state abbreviation and this used for plotting on the map visual in PBI. We have the data category set to "Place" for this field and this all seems to work well when the data is in the proper format. Issue we're having is sometimes a park name or park abbreviation is entered for the city portion. Bing sometimes handles this flawlessly and other times plots the location on another continent. 

Question: Is it possible to have common name corrections take place where if a user entered "Park 01, CA" (instead of the city and state like requested) we could have it match with the city it's in like "San Francisco, CA" before being sent to Bing for plotting on the map in the reports? We have a handful of common names we can expect to be missentered and would like the ability to see these and correct the data automatically with the city and state. If this is possible, how could we add 2 or 3 common names to match up with certain cities and states?

[dsever04]

Fuzzy matching looks to be what I am looking for. Going to attempt to implement this during the week and I'll see where it takes me. Thank you @Greg_Deckler and @amitchandak for pointing me in the right direction!

[amitchandak]

@dsever04 , in Power BI you can use search (case insensitive) ,FIND, CONTAINSSTRING

search("ABC",'Table'[IntentName],,0)

search([Col]",'Table'[IntentName],,0)

FIND("ABC";'Table'[IntentName],,0)

CONTAINSSTRING('Table'[column1],"Postcode")

You can also explore fuzzy matching

https://www.poweredsolutions.co/2019/03/26/fuzzy-matching-in-power-bi-power-query/

[Greg_Deckler]

@dsever04 Well, couple ways. One, you could create this list and use Power Query to do a Merge potentially (even has fuzzy matching) or maybe more basically just use "replace values".

You could use DAX to do this as well, the basics are:

New Column = IF(SEARCH(<find this>, [Place],,0)>0,<replace stuff>,[Place])

Obviously you'd have to some things to cover multiple different potential replacements but that's the jist of it.

Also, I recently invented fuzzy matching in DAX to match against a pre-defined set of things. I called it Fuzzy. https://community.powerbi.com/t5/Quick-Measures-Gallery/Fuzzy/m-p/1352914#M608