Forum Discussion

jsteffe's avatar
jsteffe
Icon for Helper III rankHelper III
3 years ago
Solved

Bing map API : how to manage null results ?

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

 

Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    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

6 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago
    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's avatar
      jsteffe
      Icon for Helper III rankHelper III
      3 years ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        3 years ago

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