Forum Discussion
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 ?