Reply
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) =>
let
    Source = Web.Contents("www.overpass-api.de/api/xapi?*[maxspeed=*][bbox="&Text.From(Lon1)&","&Text.From(Lat1)&","&Text.From(Lon2)&","&Text.From(Lat2)&"]")
in
    Source
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.

 

 

 

let
    Source = Json.Document(Web.Contents("https://api.tfl.gov.uk/Line/Mode/bus/Route?app_id=******&app_key=****")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name"}, {"Column1.name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.name", "RouteID"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Stop Data", each GetData([RouteID])),
    #"Expanded Stop Data" = Table.ExpandTableColumn(#"Added Custom", "Stop Data", {"Column1.id", "Column1.name", "Column1.lat", "Column1.lon"}, {"Stop Data.Column1.id", "Stop Data.Column1.name", "Stop Data.Column1.lat", "Stop Data.Column1.lon"}),
   


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

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

Visually the columns would look like:

 

: Lon 1 : Lat1 : Lon2 :  Lat2 :

:    x1   :   y1  :   x2   :   y2   :

:    x2   :    y2 :   x3   :    y 3  :

 

 

Any ideas ?