Query erroring out when loading the lat and long from Bing API

[jimgores]

Hello,

I have been beating my head against the wall on this issue and Google hasn't solved it yet.  So, I'm turning to the experts.

I am just trying to geocode about 5,000 addresses in Power Query/Power BI and return the lats and longs for those addresses.  The addresses had some undesired characters, so I have attempted to clean, trim and replace all unwanted characters and have even tried lines to remove all characters that are not alphanumeric.  Still I'm getting errors with certain addresses even though when I run the same API call through the Bing API, I don't receive any errors.

Here is my query:

let
Source = Table.NestedJoin(#"ZEV_address_level_102020 - only active", {"vin_no"}, July2020EVDatawithattributeswithVIN, {"VIN_NO"}, "July2020EVDatawithattributeswithVIN", JoinKind.LeftOuter),
#"Expanded July2020EVDatawithattributeswithVIN" = Table.ExpandTableColumn(Source, "July2020EVDatawithattributeswithVIN", {"VIN_NO", "LRD", "US_LINE_TX", "latitude", "longitude"}, {"July2020EVDatawithattributeswithVIN.VIN_NO", "July2020EVDatawithattributeswithVIN.LRD", "July2020EVDatawithattributeswithVIN.US_LINE_TX", "July2020EVDatawithattributeswithVIN.latitude", "July2020EVDatawithattributeswithVIN.longitude"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded July2020EVDatawithattributeswithVIN", "Address Same", each if [Street_addr] = [July2020EVDatawithattributeswithVIN.US_LINE_TX] then "Match" else "Not Same"),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each [Address Same] = "Not Same"),
#"Added Conditional Column1" = Table.AddColumn(#"Filtered Rows", "Same VIN", each if [vin_no] = [July2020EVDatawithattributeswithVIN.VIN_NO] then "Same" else "Not Same"),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Zip", type text}}),
#"Remove Non-Alphanumeric Address" = Text.Combine(List.Select(Text.ToList("Street_addr")), (x) => List.Contains({"a".."z", "A".."Z", " ", "0".."9"},x)),
#"Replaced #" = Table.ReplaceValue(#"Changed Type","#","",Replacer.ReplaceText,{"Street_addr"}),
#"Trimmed Address" = Table.TransformColumns(#"Replaced #",{{"Street_addr", Text.Trim, type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Address", "Street_addr", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Street_addr.1", "Street_addr.2", "Street_addr.3", "Street_addr.4", "Street_addr.5", "Street_addr.6", "Street_addr.7"}),
#"Removed Extra Addr Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Street_addr.6", "Street_addr.7"}),
#"Replaced Nulls in Addr5" = Table.ReplaceValue(#"Removed Extra Addr Columns",null,"",Replacer.ReplaceValue,{"Street_addr.5"}),
#"Replaced UNIT in Addr5" = Table.ReplaceValue(#"Replaced Nulls in Addr5","UNIT","",Replacer.ReplaceText,{"Street_addr.5"}),
#"Replaced APT in Addr5" = Table.ReplaceValue(#"Replaced UNIT in Addr5","APT","",Replacer.ReplaceText,{"Street_addr.5"}),
#"Trimmed Addr5" = Table.TransformColumns(#"Replaced APT in Addr5",{{"Street_addr.5", Text.Trim, type text}}),
#"Cleaned Addr5" = Table.TransformColumns(#"Trimmed Addr5",{{"Street_addr.5", Text.Clean, type text}}),
#"Replaced Nulls in Addr4" = Table.ReplaceValue(#"Cleaned Addr5",null,"",Replacer.ReplaceValue,{"Street_addr.4"}),
#"Trimmed Addr4" = Table.TransformColumns(#"Replaced Nulls in Addr4",{{"Street_addr.4", Text.Trim, type text}}),
#"Cleaned Addr4" = Table.TransformColumns(#"Trimmed Addr4",{{"Street_addr.4", Text.Clean, type text}}),
#"Replaced Nulls in Addr3" = Table.ReplaceValue(#"Cleaned Addr4",null,"",Replacer.ReplaceValue,{"Street_addr.3"}),
#"Trimmed Addr3" = Table.TransformColumns(#"Replaced Nulls in Addr3",{{"Street_addr.3", Text.Trim, type text}}),
#"Cleaned Addr3" = Table.TransformColumns(#"Trimmed Addr3",{{"Street_addr.3", Text.Clean, type text}}),
#"Trimmed Addr2" = Table.TransformColumns(#"Cleaned Addr3",{{"Street_addr.2", Text.Trim, type text}}),
#"Cleaned Addr2" = Table.TransformColumns(#"Trimmed Addr2",{{"Street_addr.2", Text.Clean, type text}}),
#"Trimmed Addr1" = Table.TransformColumns(#"Cleaned Addr2",{{"Street_addr.1", Text.Trim, type text}}),
#"Cleaned Addr1" = Table.TransformColumns(#"Trimmed Addr1",{{"Street_addr.1", Text.Clean, type text}}),
#"Remove Non-Alphanumeric City" = Text.Combine(List.Select(Text.ToList("City")), (x) => List.Contains({"a".."z", "A".."Z", " ", "0".."9"},x)),
#"Trimmed City" = Table.TransformColumns(#"Cleaned Addr1",{{"City", Text.Trim, type text}, {"Street_addr.2", Text.Trim, type text}}),
#"Cleaned City" = Table.TransformColumns(#"Trimmed City",{{"City", Text.Clean, type text}}),
#"Replaced Nulls in City" = Table.ReplaceValue(#"Cleaned City",null,".",Replacer.ReplaceValue,{"City"}),
#"Remove Non-Alphanumeric Zip" = Text.Combine(List.Select(Text.ToList("Zip")), (x) => List.Contains({"a".."z", "A".."Z", " ", "0".."9"},x)),
#"Trimmed Zip" = Table.TransformColumns(#"Replaced Nulls in City",{{"Zip", Text.Trim, type text}}),
#"Cleaned Zip" = Table.TransformColumns(#"Trimmed Zip",{{"Zip", Text.Clean, type text}}),
#"Replaced Nulls in Zip" = Table.ReplaceValue(#"Cleaned Zip",null,".",Replacer.ReplaceValue,{"Zip"}),
#"Remove Non-Alphanumeric State" = Text.Combine(List.Select(Text.ToList("State")), (x) => List.Contains({"a".."z", "A".."Z", " ", "0".."9"},x)),
#"Replaced Nulls in State" = Table.ReplaceValue(#"Replaced Nulls in Zip",null,".",Replacer.ReplaceValue,{"State"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Nulls in State",{"Index"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "CombinedAddress", each [Street_addr.1]&" "&[Street_addr.2]&" "&[Street_addr.3]&" "&[Street_addr.4]&" "&[Street_addr.5]&" "&[City]&" "&[State]&" "&[Zip]),
#"Added Conditional Column2" = Table.AddColumn(#"Added Custom", "Location", each if [county_nm] = "Out of US" then "550 Capitol St NE Salem OR 97301" else [CombinedAddress]),
#"Added Custom1" = Table.AddColumn(#"Added Conditional Column2", "Geocoding", each FindLatLong(Text.Replace([Location], "&", " & "))),
#"Expanded Geocoding" = Table.ExpandTableColumn(#"Added Custom1", "Geocoding", {"Latitude", "Longitude"}, {"Latitude", "Longitude"})
in
#"Expanded Geocoding"

(Yes, it probably has excessive cleaning code in it by now, but that reflects my frustration.) 

Everytime I run the query I get the following error:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] Web.Contents failed to get contents from 'http://dev.virtualearth.net/REST/v1/Locations/address?o=xml&key=key' (500): Internal Server Error.
'.

(Error edited to remove address and API key.) When I copy and paste the url provided in the error, I get the anticipated xml with lat and long back from Bing with no errors.

When I go to a row that throws the error and try to expand it I get the following message:

Finally, here is the "FindLatLong" query I am referencing.  It is not mine, but comes from a very reliable source.

let

FindLatLong = (address as text) =>

let
Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/"&address&"?o=xml&key=DJJ82p3LHH4HTKzsv5px~ydIBDUB-O8_d5glLaskYfA~AsXP1dyod9Sh-Stg9cu3ojRhPzakQRwspxuYHHX9-qv0cOVvTJOm5q4AAfFTkMNJ")),
#"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}})
in
#"Changed Type3"
in
FindLatLong

 Why do only some addresses throw errors?  Is there a way to just skip the errors (i.e. return 0's for lat and long) and have the query just move on? Does anyone have any ideas?  I'm stuck.

Thank you in advance!

Jim

[V-pazhen-msft]

@jimgores 
Regarding to the datasource error you could refer the following methods, this error can be caused by many reasons, there is no specific fix to this issue, you have to troubleshoot step by step.

"Web.contents failed to get content from site URL" displayed after trying to establish a connection ...
Solved: DataSource.Error Web.Contents failed to get data f... - Microsoft Power BI Community

For the address error, this is most likely because you had multiple data types in one column, for example the " " blank is record as text but other value(lat/long) could be integer. You can try replace " " to null or replace error to null.

Solved: Cannot convert value null to type List - Microsoft Power BI Community

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[jimgores · Accepted solution]

I have fixed some of my problems using "try" and "otherwise" as explained in this post:

https://community.powerbi.com/t5/Desktop/Get-Address-from-Lat-Long-in-Bing-maps/m-p/1245768

However, some completely legitimate addresses still throw errors.

[jimgores]

Update:

The "try" and "otherwise" additions to the code fixed the error issues described above.  The remaining errors are truly random and seem to be related to how my computer communicates with the Bing API and are unrelated to this topic.  Therefore, I would mark this issue solved.