Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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().
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().
Thanks, that exactly what I was trying to work towards. I was so close!