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
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.
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?