Bing map API : how to manage null results ?

[jsteffe]

I get this custom function that gives the address based on latitude and longitude using the BING MAP API :

let

    FindLoc= (lat, lon) =>

let
    Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/"&Replacer.ReplaceText(Text.From(lat),",",".")&","&Replacer.ReplaceText(Text.From(lon),",",".")&"?o=xml&key=AqXbM3RmYA05LA89D0jBdRSYxgrYNUOwSNEsDS2G9aXaYBqFDOIYDT8IPoHKhJRp")),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
    #"ResourceSets développé" = Table.ExpandTableColumn(#"Type modifié", "ResourceSets", {"ResourceSet"}, {"ResourceSets.ResourceSet"}),
    #"ResourceSets ResourceSet" = #"ResourceSets développé"{0}[ResourceSets.ResourceSet],
    #"Type modifié1" = Table.TransformColumnTypes(#"ResourceSets ResourceSet",{{"EstimatedTotal", Int64.Type}}),
    Resources = #"Type modifié1"{0}[Resources],
    Location = Resources{0}[Location]  
in
    #"Location"

in
    FindLoc

It works well but if my GPS point is in the ocean, there is no returned value and it generates an error.

How can I solve this problem ?

Thanks for yous help,

Jérôme

[lbendlin]

let

    FindLoc= (lat, lon) =>

let
    Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/"&Replacer.ReplaceText(Text.From(lat),",",".")&","&Replacer.ReplaceText(Text.From(lon),",",".")&"?o=xml&key=AqXbM3RmYA05LA89D0jBdRSYxgrYNUOwSNEsDS2G9aXaYBqFDOIYDT8IPoHKhJRp")),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
    #"ResourceSets développé" = Table.ExpandTableColumn(#"Type modifié", "ResourceSets", {"ResourceSet"}, {"ResourceSets.ResourceSet"}),
    Location = try #"ResourceSets développé"{0}[ResourceSets.ResourceSet]{0}[Resources]{0}[Location][Name]{0}  otherwise "undefined"
in
    #"Location"

in
    FindLoc

[jsteffe]

Thanks for your answer which perfectly works.

But I I wan to get 2 columns (lacationName and AdressLocality  for example), should I create 2 function (one for each information) or can I return 2 columns (or a table) as the result of my function ?

[lbendlin]

Yes, you can modify my proposal to return a table rather than a scalar value, for example via Table.SelectColumns

[jsteffe]

As I never used Dax functions before, could you please give me the complete syntax I should used in my function please ?

Thanks a lot .

[lbendlin · Accepted solution]

That's not DAX, it is Power Query.

let
    FindLoc= (lat, lon) =>
let
    Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/"&Replacer.ReplaceText(Text.From(lat),",",".")&","&Replacer.ReplaceText(Text.From(lon),",",".")&"?o=xml&key=AqXbM3RmYA05LA89D0jBdRSYxgrYNUOwSNEsDS2G9aXaYBqFDOIYDT8IPoHKhJRp")),
    #"Removed Other Columns" = try Table.SelectColumns(Source{0}[ResourceSets]{0}[ResourceSet]{0}[Resources]{0}[Location]{0}[Address],{"FormattedAddress", "Locality"}) 
    otherwise #table({"FormattedAddress","Locality"},{{"undefined","undefined"}})
in
    #"Removed Other Columns"
in
    FindLoc

[jsteffe]

Thanks a lot for your help which was so helpful !