Geocoding with power query in excel and bing

[bbrimberry]

Hello fellow Excel lovers,


I am working on a custom power query function that will geocode addresses using bing maps. It works great with one exception. Bing is returning more than 1 match at times.

I only need 1 match. I'm not sure my URL is quite right.

Can someone advise?






let



findlatlong =(myLocation) =>



let

Source = Xml.Tables(Web.Contents(http://dev.virtualearth.net/REST/v1/Locations/&myLocation&"?o=xml&maxResults=1&key=mykey")),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),

#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ResourceSets"}),

#"Expanded ResourceSets" = Table.ExpandTableColumn(#"Removed Other Columns", "ResourceSets", {"ResourceSet"}, {"ResourceSet"}),

ResourceSet = #"Expanded ResourceSets"{0}[ResourceSet],

#"Changed Type1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}),

Resources = #"Changed Type1"{0}[Resources],

#"Expanded Location" = Table.ExpandTableColumn(Resources, "Location", {"Confidence", "GeocodePoint"}, {"Confidence", "GeocodePoint"}),

#"Expanded GeocodePoint" = Table.ExpandTableColumn(#"Expanded Location", "GeocodePoint", {"Latitude", "Longitude", "UsageType"}, {"Latitude", "Longitude", "UsageType"}),

#"Removed Columns" = Table.RemoveColumns(#"Expanded GeocodePoint",{"UsageType"})

in

#"Removed Columns"



in findlatlong

 

[brawnystaff]

When applied to a single column with addresses, returns a "Table" value in column. Rather than expanding, create a Custom Column and use Table.First formula to extract the first value only.

 

[bbrimberry]

thanks for the reply!

I'm happy with the number of columns that are being returned, I only want bing maps to return one row per address.

the maxResults=1 part of the URL string doesn't seem to be working