Expand custom column nested in JSON record - API

[bejazep]

I have a table with Latitude and Longitude fields and would like to retrieve the country associated with the latitude and longitudes from a column created by an invoked function using an API. I am using the Bing maps API to get the location information from the point provided, however the information is stored within nested tables and I'm not sure how to get the data into it's own column in the original table. Power BI is only allowing me to "Expand to new rows", which leads to multiple problems:

    1) when I expand rows there are 4 times the number of rows I started with, 

    2) some of the countries when fully expanded are incorrect (says France when the actual country is not), and 

    3) there are multiple rows for what was initially 1 row, so there is no way to identify the correct initial row or filter out the wrong rows (some locations might be in France so I cannot just filter by that).

I would like to make a column with the correct Country for the given set of coordinates, which seems to be located in the address table of the second Record of each coordinate pair's list. The other records hold an address in France for some reason.

When I try to "Expand to new rows", I get multiple rows for 1 row and France seems to always show up (the other Records have an address of France, so I assume that is what is expanded. It should only be Poland for the below coordinates).

[v-chuncz-msft · Accepted solution]

@bejazep,

The following custom function works for me, you may have a look.

let
    LocationByPoint = (latitude as text, longitude as text) =>
let
    Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/" & latitude & "," & longitude & "?includeEntityTypes=countryRegion&o=xml&key=BingMapsKey")),
    ResourceSets = Source{0}[ResourceSets],
    ResourceSet = ResourceSets{0}[ResourceSet],
    Resources = ResourceSet{0}[Resources],
    Location = Resources{0}[Location]{0}[Name]
in
    Location
in
    LocationByPoint

[bejazep]

Yes, this works to expand the columns! Thank you so much for explaining how to get the nested values. However after getting the nested values, some of the values were incorrect (not due to your method, the returned data was just not uniform in the way I had initially thought). Some default countries showed up as France, which is incorrect, so the target record was not always the second index... there was a lot of variation.

I was able to get the correct country by modifying the Bing maps API request to only include the countryRegion field, and the request was incorrect syntax because I needed to remove "point=".

Thanks again for your help!