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 via a data validation allows them to select start and end point via a drop down list. Now the Power Query FunctionGetPoint gets these two locations and the API "https://distance geoportail lu..." looks up the distance from their database and returns this to the excel (see picture below).

My problem is that I do not know how to allow for multiple rows to be considered by the API. Do I have to make a separate table for each row or do I need to create a loop to go through all the rows where there is a start and endpoint ?

Any help or advice is much appreciated.

Thank you in advice.

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:

     

14 Replies

    • 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

    Hi,

    I started your original file with the 4 locations already entered as per your Excel worksheet and added a new start and endpoint. Uppon clicking on Refreh, all distances dissappeared.

    Did I do something wrong?

    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      Open Power Query and send me the error (if there is some), but you will need to login to that site via Power Query as Anonymous for the first time. 

      • Phil225's avatar
        Phil225
        Regular Visitor
        2 years ago

        Perhaps it is a compatibility error. We work with O365 for Business and have the corresponding Excel version. I have several errors (I opened you original file and clicked on Refresh All):

        1. The compatibility Warning

        2. The name "Web.BrowserContents.....". This I changed in Web.Page(Web.Contents("https://....) and

        3. As soon as I made the above changes, the name "Html.Table" error was displayed.

         

        Perhaps this helps to understand the issue on my side.

        Thank you again for your time!