Forum Discussion

DavidWaters's avatar
DavidWaters
Icon for Helper I rankHelper I
5 years ago
Solved

Using Bing Maps REST services to validate locations

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

  • DavidWaters's avatar
    DavidWaters
    5 years ago

    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

3 Replies