Pulling multiple API lines using a list of ID references

[DaveHayter]

Hello,

I have two API lookups that I need to try and work with. I can pull them both individually, but what I need is as follows;

API 1 returns to me a list of all certain locations within an area, giving their names and reference numbers.

API 2 uses a single one of the location reference numbers within its source line to return data for that one location.

What I ideally need to do is run through the return table from the first API, giving me the whole list of locations and their ID numbers, and then use the list of those ID numbers to run through each of the lines into the second API and compile a new table showing all of the details for each of those ID numbers in one data table.

Is this possible at all?

Thanks,

[danextian]

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.

[DaveHayter]

Right, 

I have been playing around with this and I feel that I am really not that far off being able to mark you as the solution! 

One issue I am having now is that I am getting the error "Expression.Error: Access to the resource is forbidden." So I need to get the api subscription key in to the custom column step to allow me to query the second API area...any suggestions how to fit that  into your suggested custom column step?

Thanks in advance!

[v-veshwara-msft]

Hi @DaveHayter ,

Thanks for reaching out to Microsoft Fabric Community and thanks for the update.

If the API requires an authentication key, you can include it securely without hard-coding the key in the query. Power Query supports using the ApiKeyName option with Web.Contents, which allows you to store and manage the key through the Web API authentication method rather than embedding it directly in the M code. For example:

Web.Contents("https://contoso.com/api/customers/get", [ApiKeyName = "api_key"])

When configured this way, Power BI will prompt for the key in the credential dialog, and the value will be stored securely. You can refer to the official documentation here: Web.Contents - PowerQuery M | Microsoft Learn

If the API expects the key in a header rather than a query string, you can continue using the Headers record inside Web.Contents, for example "Ocp-Apim-Subscription-Key" or "x-api-key", depending on the API specification.

Subscriptions in Azure API Management | Microsoft Learn

Using Table.AddColumn() to call the second API for each record works fine. Just make sure the main part of the API URL stays the same and only the changing parts (like IDs or dates) are passed through RelativePath or Query. This helps avoid refresh issues in the Power BI service.

You can find more details on structuring API calls securely in Power Query here:

Web.Contents - PowerQuery M | Microsoft Learn

Handling authentication for Power Query connectors - Power Query | Microsoft Learn

Similar error: Solved: Access to the resource is forbidden error while ac... - Microsoft Fabric Community

Hope this helps. Please reach out for further assistance.

Thank you.

Thanks @danextian for sharing your valuable inputs.

[v-veshwara-msft]

Hi @DaveHayter ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.

[DaveHayter]

So after i Posted this request, I managed to make a column in my data from API1 to give me all of the individual API "Addresses" for each of the locations, so if I copy each one and put it into my API call for the second API then I can manually get each locations data.

With this is mind, will looping through all of these (a list of all the required API URLs) be an easier process?

[GrowthNatives]

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] 

[DaveHayter]

So how would i specifically achieve this? I am kind of new to API