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

[bh98381]

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

[v-piga-msft · Accepted 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

[bh98381]

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.