Using data from one API endpoint to query another many times

[user349287]

I’m fetching data via API into Power BI, and I’ve come across this issue: I need to be able to query one endpoint based on data from another endpoint. 

One endpoint has a listing of several hundred IDs. I need specific status information about these IDs that only exists in another endpoint. The querying looks like this: 

Listing of IDs:

https://endpoint_url.com/ID_list 

ID status:

https://endpoint_url.com/<Individual ID>/id_status

How can I leverage Power Query to take each ID value from the resulting list of the first request, query the second URL with each ID value where it needs to go in the URL, and return a list of all the IDs with their statuses? Is this possible with Power Query alone? Will I need to leverage Python? 

Many thanks for reading 🙏

[rubayatyasmin · Accepted solution]

Hi, @user349287 

Yes, it is possible to achieve this in Power Query without the need to leverage Python. Power Query has functionalities that allow you to make multiple API calls and combine data from different endpoints. Here's how you can do it:

Step 1: Fetch the ID list from the first endpoint Create a new query in Power BI and use the "Web" connector to fetch the data from the URL: https://endpoint_url.com/ID_list. This should give you a table with a column containing the IDs.

Step 2: Transform the data Transform the data in Power Query to extract the IDs from the table. You may use the "Extract Values" option to extract the IDs from the column.

Step 3: Loop through the IDs and fetch status information Create another query that loops through the extracted IDs and constructs the corresponding URL for each ID to fetch the status information. This can be done using the "Invoke Custom Function" option in Power Query.

Step 4: Combine the data In the same query as step 3, combine the data retrieved for each ID into a single table. You can do this using the "Table.Combine" function or other appropriate merging functions based on your data structure.

should work in most cases, it's important to note that Power Query has limitations in terms of rate limiting and handling large datasets. If you encounter any performance issues or restrictions, you may consider leveraging Python through the "Python Script" option in Power Query.