Forum Discussion

bh98381's avatar
bh98381
Regular Visitor
7 years ago
Solved

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-query-and-power-bi/

 

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

  • 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

     

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar

    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's avatar
      bh98381
      Regular Visitor

      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.