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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Syndicate_Admin
Administrator
Administrator

Using the results of one Web query to hit multiple Web query endpoints

I'm trying to use the results of one web query to run a series of web queries, and ideally append all the results.  Here is the general scenario:

 

Web endpoint: "https://127.0.0.1/SalesRegions"

Returns: "London, Paris, New York, Tokyo" ... up to X results.  Could be hundreds.

 

Web endpoint: "https://127.0.0.1/SalesRegion/"

is the list of results from the previous query.  There could be 1 to hundreds of endpoints we need to hit.

Returns: A list of rows and columns.  For example "SaleDate, Price, Item"

 

So I'm essentially looking for two steps.  Firstly how do I use the results of the first query to run multiple subsequent queries.  I understand how to do it if the Sales Regions are known in advance -- just create a bunch of static queries.  But I have no way of knowing what the first query will return and it could be hundreds of endpoints.

 

The second step is simply how to append all of these query results into one table, when the total number of queries isn't known in advance.

 

TIA if anyone can help at all!!!

6 REPLIES 6
Syndicate_Admin
Administrator
Administrator

Oh wow, thank-you.  Will take me some time to digest everything but I really appreciate this so much!

Syndicate_Admin
Administrator
Administrator

RegionNameField1Field2Field3
Parisloremipsumdolor
Parissitametconsectetur
Parisadipiscing elitsed

 

Formatted in JSON of course.  The contents of the table are immaterial, what's important is that I:

 

1. I have to call a REST API that will give me a list of endpoints I need to query.  There is no way to know in advance what these endpoints will be, and there could be hundreds of them.   So I can't use hard-coded data sources.

2. Every endpoint will return a table with an identical number of fields, and all the fields are the same name.  I need to smash together all these individual tables into one large one.

See attached for the boilerplate code.

Syndicate_Admin
Administrator
Administrator

Sorry any chance you could elaborate a bit?  I can create a custom column that's formatted as a valid endpoint but what do you mean by "call" the endpoing?

 

So for example I've got a table that looks like this:

 

tblRegions

RegionNamecstEndPoint
Parishttps://127.0.0.1/SalesRegion/Paris
Londonhttps://127.0.0.1/SalesRegion/London
Milanhttps://127.0.0.1/SalesRegion/Milan
Topekahttps://127.0.0.1/SalesRegion/Topeka

 

Calling each of those 4 endpoints will return 4 tables that all use the same schema.  But how do I actually call all of these endpoints in a query and how do I Append the results of all these results together into a single table if I have no way of knowing in advance what tblRegions will contain and it could be hundreds of records?

 

I'm sorry if I'm being really simplistic, I'm still very new to all this.

please post a sample response from one of these urls.

lbendlin
Super User
Super User

Convert the list from the first result to a table, then add a custom column that calls the web endpoint for each of the table rows.  Combine results as appropriate.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors