Forum Discussion

Phil225's avatar
Phil225
Regular Visitor
2 years ago
Solved

How to add multiple rows in to get data from an external source via an API

Hello, I need to create a list where colleagues can enter a startpoint and endpoint in order to calculate the distance and hence the travel costs. The API gets the locations they can choose from and...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    1.) use this code for fnGetDistance (this one is significantly faster)

     

     

    (start as text, end as text)=>
let
    Source = Json.Document(Web.Contents("https://distance.geoportail.lu/webservice/" & start & ":" & end & "?format=json")),
    Calculated = Source[calculated]
in
    Calculated

     

     

     

    2.) now I see the error for Output query -
    Now we have two options:
    a.) you have to turn off the firewall (you need to do this for every user who will use this query)

     

     

    b.) replace whole code for Output query with this one:

     

    let
    Source = Excel.CurrentWorkbook(){[Name="t_EnteredPoints"]}[Content],
    FilteredRows = Table.SelectRows(Source, each not List.Contains({[startpoint], [endpoint]}, null)),
    Ad_Distance = Table.AddColumn(FilteredRows, "distance", each fnGetDistance([startpoint], [endpoint]), type number),
    ChangedType = Table.TransformColumnTypes(Ad_Distance,{{"startpoint", type text}, {"endpoint", type text}})
in
    ChangedType

     

     

    with this b.) option you will need to few times to click IGNORE privacy:

     

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi, you can enter multiple points and click refresh on Calculated Distance. (See attached .xlsx file)

 

 

Phil225's avatar
Phil225
Regular Visitor
2 years ago

Hello,

 

Thank you for your reply.

 

My objective is to have a calculated distance on each row based on the corresponding start and endpoint in that same row, like in the picture below:

Like this, my colleagues can enter multiple travels (in each row) for the complete month.

I can not extend the table range of the Distance cells. After each refresh, the table returns to the single cell value only.

 

Thank you again for your advice.

 

Best regards.

    • Phil225's avatar
      Phil225
      Regular Visitor
      2 years ago

      Thank you, I will try to understand how this works and see if I can add new start/endpoints in the table with the distance being calculated via the API.

      Would it be okay if I ask again in case I do not understand how this is done?

       

      Best regards.

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        You're welcome. Of course you can add new start/endpoints 😉 Let me know if you have questions.