Pulling multiple API lines using a list of ID references

Hi DaveHayter , 

Yes — what you’re describing is absolutely possible. This is a very common pattern when working with APIs:

API 1 → gives you a list of locations and their IDs.

API 2 → requires one ID at a time to get details for that location.

The goal is → loop through all IDs from API 1, call API 2 for each, and build one big combined dataset.

Explaining the general workflow of the API's :

Call API 1 → retrieve all locations + IDs.
Example response (simplified):

[
{"id": "123", "name": "Location A"},
{"id": "456", "name": "Location B"}
]

Loop through IDs → for each id, call API 2.
Example API 2 response for id=123:

{
"id": "123",
"name": "Location A",
"status": "Active",
"capacity": 42
}

Store results → each API 2 result goes into a list (or table).

Combine into a single dataset → could be a Pandas DataFrame, CSV, Excel, or database table.


⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore More] 

Hi DaveHayter 

Start with the first API, the API of locations. Once you have those create a custom column that references the API from the locations generated from the first API

= Table.AddColumn(#"Changed Type1", "JSON", each let
start = Date.ToText(StartDate, "MM-dd-yyyy"),
end = Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), - 1),"MM-dd-yyyy"),

    url = "https://stockdata.domain.tld",
    body = "{" &
           """cmpy_id"":""" & [Page] & """," &
           """security_id"":""" & [SecurityID] & """," &
           """startDate"":""" & start & """," &
           """endDate"":""" & end & """}",
    headers = [
        #"Content-Type" = "application/json",
        Accept = "application/json"
    ],
    response = Web.Contents(url, 
    [
        RelativePath = "common/DisclosureCht.ax", 
        Headers = headers,
        Content = Text.ToBinary(body)
    ]),
    jsonResponse = Json.Document(response)
in
    jsonResponse)

The startDate parameter is formatted as MM-dd-yyyy and passed to the start variable, while end represents the current date in the same format. The table already includes the columns [Page] and [SecurityID]. These parameters are then used to query a keyless API that provides stock data.

Your actual query will of course vary.  Note: it is essential to use Web.Contents' RelativePath to avoid a dynamic data source which cannot be refreshed in the service.

Hi DaveHayter ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.