Custom Column to call API for getting coordinates

Power BI Software & Tools
1

Hi there,
I am trying to pass in city name and country to a remote API call and get coordinates (longitude/latitude) .

How do I achieve this by creating custom columns?

There is a similar thing someone posted in here:

But I am not sure how to do it practically. Any advice is appreciated.

HH

1 Like
2

Can you try it on Bing. Power BI is working seamlessly with Bing maps.

3

Hi @hhooi we’ve noticed that no response has been received from you since the 20th of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

4

Thanks, @piryani I will check more on Bing

5

Hi @hhooi

For getting Langitude and Latitued based on Country and City using Bing APi, you can follow below steps.

  1. Go to https://www.bingmapsportal.com/
  2. Create an Account
  3. Go to My Account- My Keys - Create a New Key and Copy it.
  4. Once you have generated key, create an APi Query like below and use as a Web Source in Power BI.

http://dev.virtualearth.net/REST/v1/Locations?countryRegion=India&locality=Delhi&o=xml&key=**YourKey**

As you are looking for getting details for multiple country and cities, I have created a Function in Power BI.

let fnAddress = (Country as text,City as text) as table =>
let
    BingKey = "AhVzYbelYPyvqADPMu7I_QWPs1vJVCkmpopoCuM0UQSUJ7Uu4FUhBFj2nzeezvZX",
    Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations?countryRegion="&Country&"&locality="&Country&"&o=xml&key="&BingKey&"")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
    ResourceSets = #"Changed Type"{0}[ResourceSets],
    ResourceSet = ResourceSets{0}[ResourceSet],
    Resources = ResourceSet{0}[Resources],
    Location = Resources{0}[Location],
    Point = Location{0}[Point],
    #"Changed Type3" = Table.TransformColumnTypes(Point,{{"Latitude", type number}, {"Longitude", type number}})
in
    #"Changed Type3"
    in fnAddress

Now, we can call this function in your source by passing Country and City as value. This will return Latitude and Longitude for all. See sample below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLyUxU0lFySc3JyFSK1YlWCg0Gcv1SyyPzi7IRAs6JOZlp+UV5QMWxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, City = _t]),
    Custom1 = Table.AddColumn(Source,"Address",each GetLocation([Country],[City])),
    #"Expanded Address" = Table.ExpandTableColumn(Custom1, "Address", {"Latitude", "Longitude"}, {"Address.Latitude", "Address.Longitude"})
in
    #"Expanded Address"

image
image939Ă—197 11.7 KB

EDNA_BingAPi_Solution.pbix (25.9 KB)

Thanks
Ankit J

3 Likes
6

Hi,

That’s not the ask. In fact, quite often the problem is that bing maps cannot resolve the coordinates properly so you want to first get the coordinates to then use the bing maps ingegration based on that data.

Did it before with Google maps api. If you have a lot of data, you need to make sure some kind of throttling is place and a way to only query what you haven’t retrieved yet.

7

Hi @hhooi, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

8

Hi,

Just read your post and to be on the safe side, are you aware that you shared your Bing API key in the function you created?
I guess this is not what you want, particularly because you may encounter an issue with too many API calls etc.
If you have not, suggest you create another one for your own use.
Paul

9

Hi @Paul - I created the key just for this ticket. I have disabled it, thanks for suggestion.

Thanks
Ankit Jain