Forum Discussion
Pulling multiple API lines using a list of ID references
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.
Super UserHi 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]
So how would i specifically achieve this? I am kind of new to API