Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Working on a Power Query Function that will return the Latitude and Longitude for a given location. It's working great, but when I get a 404 error from the Bing Maps API the refresh crashes. I'd like to add some error handling to the function so that when Bing Maps API returns a 404 then the function will just return a null for the Lat and Long and move on to the next call.
I have found a great article on Chris Webbs blog that covers handling a 404 error here:
Here's my function that returns an error (I've replaced teh API Key with "XXX"):
let FindLatLong = (location) => let Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/"&location&"?o=xml&key=XXX")), #"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}}), Point = #"Changed Type2"{0}[Point], #"Changed Type3" = Table.TransformColumnTypes(Point,{{"Latitude", type number}, {"Longitude", type number}}) in #"Changed Type3" in FindLatLong
I've tried to work in the solution from the blog, but I'm running into an issue on the if statement. The error is Token Literal expedcted.
let FindLatLong = (location) => let Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/"&location&"?o=xml&key=XXX", [ManualStatusHandling={404}]), GetMetadata = Value.Metadata(Source), GetResponseStatus = GetMetadata[Response.Status], Output = if GetResponseStatus=404 then "Error!" else Source #"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}}), Point = #"Changed Type2"{0}[Point], #"Changed Type3" = Table.TransformColumnTypes(Point,{{"Latitude", type number}, {"Longitude", type number}}) in #"Changed Type3" in FindLatLong
Not sure where to go with this now. Any help is appreciated.
Thanks!!!!
Solved! Go to Solution.
Hi @bh98381 ,
By my tests and research on your query, the issue is caused by lacking of ")".
Please try the query below.
let FindLatLong = (location) => let Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/"&location&"?o=xml&key=XXX", [ManualStatusHandling={404}])), GetMetadata = Value.Metadata(Source), GetResponseStatus = GetMetadata[Response.Status], Output = if GetResponseStatus=404 then "Error!" else Source, #"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}}), Point = #"Changed Type2"{0}[Point], #"Changed Type3" = Table.TransformColumnTypes(Point,{{"Latitude", type number}, {"Longitude", type number}}) in #"Changed Type3" in FindLatLong
Here is the test result.
Hope this can help you.
Best Regards,
Cherry
Hi @bh98381 ,
By my tests and research on your query, the issue is caused by lacking of ")".
Please try the query below.
let FindLatLong = (location) => let Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/"&location&"?o=xml&key=XXX", [ManualStatusHandling={404}])), GetMetadata = Value.Metadata(Source), GetResponseStatus = GetMetadata[Response.Status], Output = if GetResponseStatus=404 then "Error!" else Source, #"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}}), Point = #"Changed Type2"{0}[Point], #"Changed Type3" = Table.TransformColumnTypes(Point,{{"Latitude", type number}, {"Longitude", type number}}) in #"Changed Type3" in FindLatLong
Here is the test result.
Hope this can help you.
Best Regards,
Cherry
Thanks @v-piga-msft
That ")" fixed the issue.
Great catch!!!
Now I'm running into a new error:
I'll try to work through this one now.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
12 | |
11 | |
7 |
User | Count |
---|---|
42 | |
27 | |
14 | |
13 | |
13 |