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

[Phil225]

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.

[Phil225]

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]

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]

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!

[dufoq3]

Try to create new query from web and paste there this address: 

https://distance.geoportail.lu/webservice/Alf:Altwies?format=string

What is the result and what query it creates?

[Phil225]

Hi,

Is this the information you need ?

Thank you.

[dufoq3]

Ok, replace whole code of fnGetDistance with one of this codes in advanced editor and let me know (I would prefer v1)

v1

(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

v2

(start as text, end as text)=>
let
    Source = Web.Page(Web.Contents("https://distance.geoportail.lu/webservice/" & start & ":" & end & "?format=string")),
    Data0 = Source{0}[Data],
    Children0 = Data0{0}[Children],
    Children1 = Children0{1}[Children],
    ChangedType = Table.TransformColumnTypes(Children1,{{"Text", type number}}, "en-US"),
    Text = ChangedType{0}[Text]
in
    Text

[Phil225]

I replaced your code and here is the result:

I will be in a meeting now until the end of the day. It's great that you keep helping me, this is well appreciated. Enjoy your weekend and I'll look forward starting my PQ course next week.

Thank you again for your time !

[dufoq3 · Accepted solution]

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]

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

[Phil225]

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.

[dufoq3]

Check attached file

[Phil225]

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]

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

[Phil225]

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