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: