Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calling multiple API Endpoints in single Query

I have created a blank query in Power BI and have successfully tested the pulling of a single url with this basic API call syntax. My problem is that I want to pull the json content of possibly hundreds of URL endpoints into a single table. Here's my syntax:

 

Let

    url = “insert url here”,

    token = “insert token here”,

    timeout = 10,

 

   data = Table.FromRecords(

        Json.documents(

             Web.Contents(

                   url,

                         [

                                 Headers =

                                 [

                                             #”Authorization”=”Bearer “ “&token,

                                             #”Content-Type”=”application/json”

                                  ],

                                 Timeout = #duration(0,0,timeout,0)

                         ]

                   )

             )

        )

In

   data

 

Apologies for spacing.. I typed it freehand, directly from my template. Also everything I am pulling is json, so no worries on dealing with any other format. 
when url equals for example /v1/students/{id}/details where {id} is the unique student identifier, lets say those are 1,2,3,4,5,6...1000. The total number of students is arbitrary. I can successfully pull the content from /v1/students/1/details and /v1/students/2/details etc... on an individual basis...
I tried concatenating URLs similar to this: 

url = "https://blahblahdotblah/thing/api/v1/students/1/details","https://blahblahdotblah/thing/api/v1/students/2/details","https://blahblahdotblah/thing/api/v1/students/3/details", and i have played around with ( ) and all of these things break. 

I need to know how to call multiple URLs into 1 table....  pulling 1000 tables (one per query) and then joining/apending them on the backend will be very resource intensive and may not provide me much benefit.

 

Extra points for a speedy solution 🙂

Seriously though, I do thank you in advance for your prompt response. This project is of utmost priority in my world. 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  Anonymous 

     

    As your URLs have a predictable structure that only the student identifier is different, you can refer to the solution from the following blog written by Mynda Treacy. 

    Scrape Data from Multiple Web Pages with Power Query • My Online Training Hub

     

    The main steps include:

    1. Convert the current query for pulling a single url to a function that can be executed for each API url
    2. Generate a list of student identifier numbers and convert it into a table
    3. Execute the function for each student identifier number

     

    For detailed steps, please refer to the above blog. Let me know if you have any question.

     

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi  Anonymous 

     

    As your URLs have a predictable structure that only the student identifier is different, you can refer to the solution from the following blog written by Mynda Treacy. 

    Scrape Data from Multiple Web Pages with Power Query • My Online Training Hub

     

    The main steps include:

    1. Convert the current query for pulling a single url to a function that can be executed for each API url
    2. Generate a list of student identifier numbers and convert it into a table
    3. Execute the function for each student identifier number

     

    For detailed steps, please refer to the above blog. Let me know if you have any question.

     

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!