cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Silko Regular Visitor
Regular Visitor

Mapping - get location country

Is it possible to get location country out of location string which doesn't include a country. Let's say I have a column of locations and I want to add a custom column with location country which I want to get from locations column. For example:

  • I have a value "Paris" in locations column
  • in location country column I want to get "France"
4 REPLIES 4
vanessafvg Super Contributor
Super Contributor

Re: Mapping - get location country

maybe you can scrape a website somewhere with that information?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
sensei Member
Member

Re: Mapping - get location country

You need more than city to get a country.  A city is not unique across a country (ex: Paris, IL USA).  Your string, at the minimum, should contain the City and State/Region.  There are paid services that will give you a list of every Country / Region / Cities / Latitude & Longitude.  I would start by concatenating the city and state/region on both sides (your table and look up table) and use that join as a look up value to find the Country. 

Highlighted
Silko Regular Visitor
Regular Visitor

Re: Mapping - get location country

I am aware of all of that but the problem is that I don't have all that information. I also know that google maps API includes a function that can return better location string (including country, city, street and post code) if you give it just a city or just a street. It gives you a suggestion it thinks it is the right one and that would be good enough. My question is, does PowerBI include such a function in M language or DAX? If there is no such a function than I will use google maps API, but it would be easier to just correct the data inside queries in PowerBI.

sensei Member
Member

Re: Mapping - get location country

I am personally not aware of any such function. My understanding is that the results are exact from the mapping calls.  It makes a determination on its own where the city belongs based on the information you provide. I could be wrong, but in the experimentation I have done, the more information I am able to plot with the city, the more accurate the results.  I have not used the street address in Power BI so I can't comment on that.

 

If Google is also taking a guess based on the info you are providing it, then you are in the same boat regardless of which API you use.  My suggestion is then to try both and see which algorithm you prefer.  Good luck.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 50 members 968 guests
Please welcome our newest community members: