Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Need Help Dynamically Creating Source URL from Data

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

21753365948103Company 1
21725603751156Company 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?

1 ACCEPTED SOLUTION
tonmcg
Resolver II
Resolver II

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().

View solution in original post

2 REPLIES 2
tonmcg
Resolver II
Resolver II

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
Not applicable

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.