Loop through list a to perform a REST API call and produce a table for each item in the list

[jh_dempsey]

Hi All

I am creating our own custom data connector to retrieve data stored in some custom software we had developed for us.

I can get to the data via a REST API

One of the REST API calls, if I provide an "assetTypeCode" parameter, will return me all records in the database for that particular asset type.

I have the asset types I want to extract in a list

eg {"DGMH","LLLP","RRVS"}

I need my data connector to loop through this list, make a REST API call using the asset code, and then create me a table containing all of the returned asset records.

This means for the example above, I should end up with 3 tables being produced. one for DMH, one for LLLP, and one for "RRVS"

The code needs to be dynamic, so if I add a 4th asset type to my list, i want the code to automatically produce me 4 tables.

Is this possible in Power BI? All m programming experience is in te .NET languages so I'm struggling with not being able to do loops!

[v-juanli-msft]

Hi @jh_dempsey 

Value the answer from mahoneypat, here is a thread how to loop web data with power query in details:

https://community.powerbi.com/t5/Desktop/script-running-looping-URLs-and-get-data/td-p/135980

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Yes.  Just start with a query that has your assettypecodes in a table (so you can refresh it to add/remove more in future), and then add a custom column that makes your web call for each.  For example,  Web.Contents("Your Base URL?assettypecode=" & [AssetTypeCode] & "other url parameters if needed").  This will return a column of Tables that you can expand to combine all the data.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat