Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jsteffe
Helper III
Helper III

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

 

1 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

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

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

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 ?

 

 

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

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 .

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

Thanks a lot for your help which was so helpful !

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors