Need Help Dynamically Creating Source URL from Data

[Anonymous]

I am working to integrate an API call into PowerBI, and it looks like this:

http://site.com/api/opportunities/

The issue is that to pull an additional array, from this, it requires me to do an additional call, on a per ID basis, like so:

http://site.com/api/opportunities/*opportunity id*/StaffTeam

I would love to automate this, that way *opportunity id* comes from the the generated by the first call above.  Is there a way to take the values from one set of data, and create an API call for every line I have?  In example, here is my data:

OpportunityId     ClientId               ClientName

2175336

5948103

Company 1

2172560

3751156

Company 2

I would love to cycle through all the OpportunityID's - add them to the /api/opportunities/*opportunity id*/StaffTeam call, and get all the data in one swoop.

Is this possible?

[tonmcg · Accepted solution]

One way to do this is to create a custom function that makes a call to your API and invoke that custom function on each row of your table. The custom function would accept each value within the OpportunityId column in the table as a parameter and would use that value to construct a valid Url to the API endpoint.

There's no way to develop a workable solution to test your use case since the http://site.com/api/ API doesn't really exist. Moreover, you haven't specified the headers the request should contain, nor have you specified the data format or structure of the response.

Despite all that, here's a query that should help get your started:

let
    /* 
    Custom function that makes a call to the API endpoint: 
    The custom function accepts an 'OpportunityId' parameter, which it uses to construct a valid Url to the API endpoint
    */
    customFunction = (OpportunityId as text) as any =>
        let
            response = Web.Contents("http://site.com/api/opportunities/" & OpportunityId & "/StaffTeam"),
            results = Json.Document(response)
        in
            results,
    // source data with an 'OpportunityId' column           
    Source = #table({"OpportunityId","ClientId","ClientName"},{{"2175336","5948103","Company 1"},{"2172560","5948103","Company 2"}}), 
    // invoke the 'customFunction' function on each row of your table and supply it the value from each row of 'OpportunityId'
    InvokedCustomFunction = Table.AddColumn(Source, "Opportunity", each customFunction([OpportunityId]))
in
    InvokedCustomFunction

I've chosen to include the custom function within the query itself. You could opt to place it in a separate query. By the way, this code assumes the API returns JSON; if it returns XML, just replace Json.Document() with Xml.Document() or Xml.Tables().

[Anonymous]

Thanks, that exactly what I was trying to work towards.  I was so close!