cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bh98381 Frequent Visitor
Frequent Visitor

Power Query Function Error Bing Maps API Token Literal Expected Error when 404 is returned

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:

https://blog.crossjoin.co.uk/2016/08/09/handling-404-not-found-errors-with-web-contents-in-power-que...

 

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!!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Power Query Function Error Bing Maps API Token Literal Expected Error when 404 is returned

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.

 

Capture.PNG

Hope this can help you.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Power Query Function Error Bing Maps API Token Literal Expected Error when 404 is returned

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.

 

Capture.PNG

Hope this can help you.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
bh98381 Frequent Visitor
Frequent Visitor

Re: Power Query Function Error Bing Maps API Token Literal Expected Error when 404 is returned

Thanks @v-piga-msft 

 

That ")" fixed the issue.  

 

Great catch!!!

 

Now I'm running into a new error:

merr.PNG

 

I'll try to work through this one now.