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

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!

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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's avatar
      Phil225
      Regular Visitor
      2 years ago

      Hi,

      Is this the information you need ?

      Thank you.

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        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