Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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 !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |