Replicating VLOOKUP approximate match to find country in address string

[maracles]

I have two tables:

Addresses: This has a single column with a list of addresses. 
Country Data Table. This is a data table of country names, continents, country codes etc

All of the addresses have a defined country and I know that the country is labelled correctly as it was chosen from a form, not typed in by a user. 

How can I therefore do an approximate match and extract the country from the address by comparing it against the country table. I want to create a secod column next to the address column for Country. 

For example with the following address:

Block 10, Villa Concerto, Symphony Bay, Sai Kung, Hong Kong

I want to compare that string against the Country Name column of my country table, match the Hong Kong part and return just that.

I have tried a different way use the Power BI maps however its ability to identify non-western addresses is hugely sub-par and many of my addresses do not appear e.g. China, India, Japan. 

[AlexChen]

Hi,

I assume you have 2 table “address” and “country” which have columns like below.

Create Cartesian Product for this 2 tables

newAddress = GENERATE('address', country)

Create a column to calculate the match item between “address” and “country”

Column = if(IFERROR(SEARCH([country], [address]), BLANK()) <> BLANK(), [country])

Now you can create a table to get matched address and country :

matchAddress = FILTER(all(newAddress[address], newAddress[Column]), newAddress[Column] <> BLANK())

Best Regards

Alex

[BhaveshPatel]

With your addresses table,

create a duplicate column, 

Split the duplicate column by Comma delimiter from the left-most side.

The country part of the address will be extracted and now you can create the if logic to compare the two columns.

Filter the rows where it matches the value.

It is dynamic and can be refreshed everytime new rows are added.

For exact locations of the addresses, there was an article written in blog by ankitpatira for geocoding of the addresses using R in powerbi would be of great help to you.

Hope this will give you the understanding of solving your problem.

[maracles]

Thanks @BhaveshPatel, I did actually think about this method, it is how I would have done it in traditional Excel however it assuming that the country is always in the same place, or last- in my address list this is not the case, I could end up with 4 or 5 different columns which all have countries in them. I could still get this method to work but it would be more time consuming than I'd like and feel there must surely be a simpler way with BI.

Do you or anyone else have any other suggestions?

As for Geociding, I've actually just been researching this vary thing using a Batch Geocoding site so I'll definitely give the article a read (this one for anyone else interested; https://community.powerbi.com/t5/Community-Blog/Geocoding-using-R-and-Google-Maps-in-Power-BI/ba-p/5...). I don't however have any experience with R so not sure how well I'll get on. 

[BhaveshPatel]

I can help you more if precise snapshot of dataset is provided.