Using Bing Maps REST services to validate locations

[DavidWaters]

Hi,

I am using the below to lookup country and postcode in my data and pass it through dev.virtualearth.net to return the Long and Lat codes.  This is working well - except one major problem.  It returns the Long and lat code of a postcode even if the country does not match - for example a row with a valid UK postcode but the country is US should fail but it seems to ignore the country!  Has anyone any experience with this or any idea how I can get it to check both country and postcode together?

Many thanks!

let
PointLookup = (Country as text, Postcode as text) =>
let

Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations?CountryRegion="&Country&"&postalCode="&Postcode&"&o=xml&key=KEY")), 

#"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],
#"Changed Type1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}),
Resources = #"Changed Type1"{0}[Resources],
Location = Resources{0}[Location],
#"Changed Type2" = Table.TransformColumnTypes(Location,{{"Name", type text}, {"EntityType", type text}, {"Confidence", type text}, {"MatchCode", type text}}),
Point = #"Changed Type2"{0}[Point],
#"Changed Type3" = Table.TransformColumnTypes(Point,{{"Latitude", type number}, {"Longitude", type number}}),

#"Result" = Table.RowCount(Source)//"True"

in
//#"Result"//#"Changed Type3"
#"Changed Type3"
in
PointLookup

[v-robertq-msft]

Hi, 

According to my research, it seems that you can try to follow these tips to improve the underlying dataset to increase the likelihood of correct geo-coding:

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-map-tips-and-tricks#in-the-dataset-tips-t...

Thank you very much!

tips to get better results when using map visualizations

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DavidWaters · Accepted solution]

Hi,

Thanks for your reply, it wasn't quite  to improve the accuracy but to enforce the lookup on both country and postcode.

I have found the answer - I needed to add strictMatch=1 to the URL

[v-robertq-msft]

Hi, @DavidWaters 

Glad to hear that you have found the solution to your problem by yourself, would you like to mark your own reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.