Forum Discussion
How to add multiple rows in to get data from an external source via an API
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 Calculated2.) 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 ChangedTypewith this b.) option you will need to few times to click IGNORE privacy:
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?
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.
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!