Calling multiple APIs based on a column

[Anonymous]

Hello - hope someone may be able to give me a steer here; I've looked around the forums, googled a bit and cannot find an answer (that I can understand/implement).

The link for the PBIX is at the bottom of this post which calls the UK Environment Agency's flood alerts from the following URL http://environment.data.gov.uk/flood-monitoring/id/floods?min-severity=3

I have expanded the list that is returned from the API call and this results in the table as seen in the PBIX.  However there's no geo-spatial data in that list, such as latitude/longitude etc which, when I have queried with the Environment Agnecy they have replied:

To get the information that you need, you will need to first call the flood warnings API as you have been doing, which returns a list of all flood warnings.

You can then use the information returned to make other calls to each flood warning in turn, using the @id field, which you will see is a url. This will return more detail about the flood warning, including spatial information - both lat and longs, and polygons.

However I have no idea how to subsequently query this '@id' to return me the dataset that I will then be able to use to plot our properties on.  I'm sure it's likely very simple but like I said I can't see how to possibly accomplish this despite having read lots of posts today.  Ideally I want to be able to query them all 'automatically' by looping through each row in the @id field to return a full list that includes the geographical i

Any help anyone could render would be awesome.  Thanks in advance

Ian

FILE:

https://drive.google.com/file/d/1oDpIcpGo0mkR79e-A2QMhjuZtKas2ups/view?usp=sharing

[parry2k · Accepted solution]

@Anonymous Hi there solution is attached. I just did the basic api call and then made an call for each id to get lat and long. You can make the changes as per your need.

Also created a small visual based on lat and lng. here it is Would appreciate Kudos 🙂 if my solution helped.

[Anonymous]

This is awesome and just what I needed, thank you so much.  Would you mid just talking me through what you did briefly?

I can see you've invoked a custom function which I think is calling the lat/long from from the id column, is that right?

Thanks again - very much appreciated

[parry2k]

@Anonymous you are right, created a function which takes ID url as a parameters and transform the data to return a table with Lat/Long.

If you need more in-depth detail, please let me know.

Would appreciate Kudos 🙂 if my solution helped.