Loop through series of APIs into single table

[Anonymous]

Hi,

I'm trying to loop through a series of API's querying a series of AssetIDs and put them into a single table. The API URLs follow a simple structure like this

https://environment.data.gov.uk/arcgis/rest/services/EA/AIMSBeachStructure/FeatureServer/0/query?whe...

https://environment.data.gov.uk/arcgis/rest/services/EA/AIMSBeachStructurePoint/FeatureServer/0/quer...

The elements of the URL that will change for each API are this

AIMSBeachStructure/FeatureServer/0

and for each AssetID are this

frms_code+%3D+%27FR%2F09%2FS048%27

Is there a way to pass a list of API elements and seperately a list of Asset ID's, maybe even from an external source and have Power BI loop through each combination to create a single table? 

Thanks in advance

[johnt75 · Accepted solution]

A custom function in power query is the way to go. If you load the results from a single call to the API you can go through all the necessary steps to clean or transform the data and then turn that entire query into a custom function, to which you will pass the Asset ID.

If you're making calls to 2 separate end points then they will probably both need to be separate functions although you may be able to combine them.

Once you have the custom functions defined you will need a table containing the Asset IDs you want to get data for. Add a column invoking your custom function using the Asset ID as a parameter then expand that new column to get the data you need.