Using Power Query: Returning Error 429

openrouteservice
1

Hi,
I’m using Power Query to get distance and travel time between data points. I’m now running into the issue of Error 429 being returned, which, if I’ve read correctly, is the error returned when too many requests/sec are made.
I’m unaware of any way of throttling Power Query (at this stage anyway) but, I need to ask if there is any way to workaround this error.

For those that may be interested, below is the M code for an API request to extract the travel time and distance.

let
Coordinates = (lat1 as text , long1 as text, lat2 as text, long2 as text) =>
let
Authkey = “Your API Key Here”,
url = “https://api.openrouteservice.org/v2/directions/driving-car/json”,
body = “{”“coordinates”“:[[” & long1 &“,”& lat1 & “],[” & long2 &“,”& lat2 &“]}”,

    Source = Json.Document(Web.Contents(url, [
        Headers=
        [#"Authorization"= Authkey, 
        #"Content-Type"="application/json; charset=utf-8",
        #"Accept"="application/json, application/geo+json, application/gpx+xml, img/png; charset=utf-8"],
        Content = Text.ToBinary( body)]
    )
    ),
    routes = Source[routes],
    routes1 = routes{0},
    summary = routes1[summary],
    #"Converted to Table" = Record.ToTable(summary),
    #"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value", List.Sum)
in
    #"Pivoted Column"
in
Coordinates
2

Hi @steved-MarketGrunt,

I’ve never seen anyone use our API in a Power Query up until now :+1:
I’m not too familiar with them, but you would somehow restrict the queries on your side to keep within the free plan limits

Not sure if you have some time or wait/sleep functions there, but you would somehow need to count the requests already made and limit them to 40/min and 2000/day.

The first google entry for power query throttling is this thread which might help you with your issue.

Best regards

3

Thank you @amandus I’ll have a look at it and get back to you. :+1:

4

Hello,
I tried the code @steved-MarketGrunt proposed.
I replaced the “Your API Key Here” with my own API Key.
But I got a 500 error : Internal Server Error.
I don’t understand why.
Could you please help me with that issue ?
Best regards

5

Hey,

when “trying the code proposed”, I assume you used your own set of coordinates.

Could you post an example for a set of coordinates you used that produced that error, so the request can be replicated?

A Server Error: 500 is usually related to an outage of some sorts, and profiles haven’t been 100% stable over the last two days, so it might be related to that.

Best regards

6

Hello,
I tried with the coordinates proposed as an example in the API playground for “Directions Service JSON (POST)”.
So {“coordinates”:[[8.681495,49.41461],[8.687872,49.420318]]}
In power query : function1(“49.41461”, “8.681495”, “49.420318”, “8.687872”) because latitude and longitude are not in the same order between the request and the code (body = “{”“coordinates”“:[[” & long1 &“,”& lat1 & “],[” & long2 &“,”& lat2 &“]}”,).
Best regards

7

Hey,

given that the default request from the API playground currently works, and that you experienced a server error, I heavily assume that it’s related to the current (albeit short) outages.

Best regards