Get Address from Lat/Long in Bing maps

[kkanda]

Hi All... Sorry.. the empty msg got posted initially.

Here is my problem. I followed some of the videos and blogs to create a function to get Address from Lat/Long data in Bing maps. I have some 1330 data points and I want to get AddressLine for all of them. The Lat/Long are "Numbers with decimals" and hence I converted them to "Text" in the function. Here is the function:

FindAddress = (Latitude,Longitude) =>
let
Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/"&Number.ToText(Latitude,"e4")&","&Number.ToText(Longi...")),
#"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}}),
Address = #"Changed Type2"{0}[Address],
#"Changed Type3" = Table.TransformColumnTypes(Address,{{"AddressLine", type text}, {"AdminDistrict", type text}, {"AdminDistrict2", type text}, {"CountryRegion", type text}, {"FormattedAddress", type text}, {"Locality", type text}, {"PostalCode", Int64.Type}})
in
#"Changed Type3"

When I use the function with the args Latitude and Longitude in the Table, I get all the additional columns as specified in the function. But after populating for about  10 rows, I got the following error msg:

Lat_Long(2) is the file name. I cannot understand the problem. Is it a problem that Bing does not find the AddressLine for some point - this is possible as some points are on the hill side and not on the streets. If so, how can I instruct the function to proceed ignoring such data points? Or is it a limitation from Bing Maps as I am using the map for too many data points? Or some other problem? I did not use Google API as it appears to be a payment mode when I wanted to generate a key. For Bing, I got the key alright.

Please assist me in getting it right.

[v-shex-msft]

HI @kkanda,

As Greg_Deckler said, it will help to add 'error handle' functions to your steps.

You need to trace which steps caused the issue and try to make an empty table with the same structure and use it as the result of this processing.

Creating Tables In Power BI/Power Query M Code Using #table() 

Regards,

Xiaoxin Sheng

[Greg_Deckler]

@kkanda 

https://docs.microsoft.com/en-us/power-query/handlingerrors

[kkanda]

Here is some sample data. First column is Latittude and Longitude.

47.766	-122.6207
48.9355	-122.6119
48.118	-122.5871
48.7597	-122.4604
46.7681	-122.4581

Data in the first four rows gets converted to Address. In the fifth row, there is no Address - I checked on the Map physically. This is where the Query stops working and throws an error.

I want to use Try ...otherwise at "ResourceSet" or "Location" line of code so that the query returns blank or null values in the generated columns and proceed with the rest of the data.

Try Location = Resources{0}[Location] otherwise MissingField.UseNull

But I am not successful as the query says "Syntax Error Token Equal expected".

Please go through my query code and suggest how to use the error trapping.

[mahoneypat]

try needs to be lowercase.  Your text shows Try

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[kkanda]

Hi mahoney... it does not work.

Any other suggestions.. the idea is to record blank or null values in the generated fields.

[kkanda]

Hi All .. I am pursuing with this thread in the hope of getting some solution. The following is the code in the Query Editor which generates the Table of results.

let
Source = Excel.Workbook(File.Contents("N:\Power BI\Documentation from IT\GPS_TestFile.xlsx"), null, true),
Lat_Long_Sheet = Source{[Item="Lat_Long",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Lat_Long_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Latitude", type number}, {"Longitude", type number}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "FindAddress", each FindAddress([Latitude], [Longitude])),
#"Expanded FindAddress" = Table.ExpandTableColumn(#"Invoked Custom Function", "FindAddress", {"AddressLine", "CountryRegion", "FormattedAddress", "PostalCode"}, {"FindAddress.AddressLine", "FindAddress.CountryRegion", "FindAddress.FormattedAddress", "FindAddress.PostalCode"}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Expanded FindAddress", {"Latitude"}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Removed Errors", {{"FindAddress.AddressLine", "NA"}}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Errors",{{"FindAddress.AddressLine", "AddressLine"}, {"FindAddress.CountryRegion", "CountryRegion"}, {"FindAddress.FormattedAddress", "FormattedAddress"}, {"FindAddress.PostalCode", "PostalCode"}})
in
#"Renamed Columns"

When I see the result table at InvokedCustomFunction, I see the following:

The 12th row does not generate "Table", it generates "Error". In the latter part of the code, the table is expanded to include additional columns. When I run the complete code, the result stops at Row 12 with Error. Going down the above table, there were more errors occuring where the Bing map could not assign an Address (on the fields or highways). 

I want to take care of these Errors with a code to resume next if such an error occurs. I am not able to write the correct code for the Address field if this throws an error. 

Has anyone got a suggestion how I can take care of these Errors?

Thanks

[kkanda · Accepted solution]

Hi All.... After several failied attempts, I think I found the solution.

On the following code line, I inserted 'try' and 'otherwise'.

#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "FindAddress", each try FindAddress([Latitude], [Longitude]) otherwise null),

This worked and all those Lat/Long positions which do not have Addresses returned 'null' and the rest of the records returned the Formatted Address.

kkanda

[Greg_Deckler]

@kkanda - Typically that would be an API call to Bing maps or Google maps. Not sure the OOTB maps do that. Maybe ArcGIS visual?