New Member
Posts: 1
Registered: ‎12-06-2018

How to refer to refer to different rows in one API call ?

Hi all,


I'm using this API Call from over pass  to find the speed limits of streets within a bounding box:


let SpeedLimits= (Lon1 as number, Lat1 as number, Lon2 as number, Lat2 as number) =>
    Source = Web.Contents("*[maxspeed=*][bbox="&Text.From(Lon1)&","&Text.From(Lat1)&","&Text.From(Lon2)&","&Text.From(Lat2)&"]")
in SpeedLimits






In the main code, I am gathering street names  and long and lats for all bus routes in london. I'd like to use the different long and lats for each stop to create a bounding box between each stop so I can then get the speed limits for all the streets within the bbox.


So currently I have four columns for the longs and lats.

I'd like to make it so the long and lats in custom column 1 and custom column 2 start from the next row of the long and lat from the data I have gathered. at the moment they just refrence the column and + 0.004 to the values for each row.




    Source = Json.Document(Web.Contents("******&app_key=****")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name"}, {""}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"", "RouteID"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Stop Data", each GetData([RouteID])),
    #"Expanded Stop Data" = Table.ExpandTableColumn(#"Added Custom", "Stop Data", {"", "", "", "Column1.lon"}, {"Stop", "Stop", "Stop", "Stop Data.Column1.lon"}),

    #"Added Custom1" = Table.AddColumn(#"Expanded Stop Data", "Lat2", each [Stop] +0.0004),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "lon2", each [Stop Data.Column1.lon] + 0.0004),

    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom2",{{"Stop", "Lat1"}, {"Stop Data.Column1.lon", "Lon1"}, {"lon2", "Lon2"}}),
    #"Added Custom3" = Table.AddColumn(#"Renamed Columns1", "Custom", each GetSpeedLimits([Lon1],[Lat1],[Lon2],[Lat2]))
  #"Added Custom3"

Visually the columns would look like:


: Lon 1 : Lat1 : Lon2 :  Lat2 :

:    x1   :   y1  :   x2   :   y2   :

:    x2   :    y2 :   x3   :    y 3  :



Any ideas ?