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:

     

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.

      • Phil225's avatar
        Phil225
        Regular Visitor
        2 years ago

        Hi,

         

        Sorry to ask again, but I removed all the values in the table on the left and selected two new locations. Now I get an error when I click on refresh all:

         

        Did I do something wrong ? Was I not supposed to delete the 4 locations in the initial file?

         

        I have never worked with Power Query before, but will start a course next week.

         

        Kind regards