cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jrickards01 New Member
New Member

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 ?

 

 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 96 members 1,462 guests
Please welcome our newest community members: