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

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

     

    • Phil225's avatar
      Phil225
      Regular Visitor
      2 years ago

      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's avatar
        dufoq3
        Community Champion
        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: