Using data from one API endpoint to query another many times

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.