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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

Using output of one Rest API in another Rest API as parameter

I am a bit new to M Query

 

I am trying to utilize output of following API

https://dev.azure.com/{organization}/{project}/_apis/work/teamsettings/iterations?api-version=6.0"

 

The API has one of the columns as iterationid for various iterations

I, then, want to use that as in input of following API in a recursive way to get capacity of all iterations

https://dev.azure.com/{organization}/{project}/{team}/_apis/work/teamsettings/iterations/{iterationI...

 

Any help would be appreciated

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @Anonymous 

 

I think it should be possible if you could already query data from two APIs in separate queries. In the query connected to the first API, remove other columns except for iterationid column. In the sencond query, connect to the second API with any iterationid and get a table result.

 

Now open the Advanced Editor of each query, you will see their M codes like below. I enter the family values into Query1 and query data from a public API from Get species - Qld wildlife data API into Query2, so the M codes are different from yours naturally. It doesn't matter.

062405.jpg

 

Then modify Query2 like below to convert it into a custom function with a parameter v_family.

062406.jpg

 

Return to Query1 and add a column by invoking custom function Query2.

062407.jpg

 

A table column will be added to Query1. You can expand the column to see all data and perform further tranformations. Attached the pbix for reference.

 

To be honest, I haven't used the APIs you mentioned. So I suggest you connect to them in separate queries first to test the connections. 

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

4 REPLIES 4
v-jingzhang
Community Support
Community Support

Hi @Anonymous 

 

I think it should be possible if you could already query data from two APIs in separate queries. In the query connected to the first API, remove other columns except for iterationid column. In the sencond query, connect to the second API with any iterationid and get a table result.

 

Now open the Advanced Editor of each query, you will see their M codes like below. I enter the family values into Query1 and query data from a public API from Get species - Qld wildlife data API into Query2, so the M codes are different from yours naturally. It doesn't matter.

062405.jpg

 

Then modify Query2 like below to convert it into a custom function with a parameter v_family.

062406.jpg

 

Return to Query1 and add a column by invoking custom function Query2.

062407.jpg

 

A table column will be added to Query1. You can expand the column to see all data and perform further tranformations. Attached the pbix for reference.

 

To be honest, I haven't used the APIs you mentioned. So I suggest you connect to them in separate queries first to test the connections. 

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

Anonymous
Not applicable

Worked like a Charm 😀

Anonymous
Not applicable

@Anonymous I am trying to do exactlly what you decsribe above: go get all Team Iterations so I can see all work items in each. The only problem is I am getting the following error in the queries: 

DataFormat.Error: We found extra characters at the end of JSON input.
Details:
Value=
Position=4

rdias_0-1626806549256.png

rdias_1-1626806566320.png

Can you please share how you used the DevOps APIs?

 

Much appreciated!

Rosie

Anonymous
Not applicable

Hi Rosie

 

I used VSTS.Contents. For eg.:

let

Source = VSTS.Contents("https://dev.azure.com....),

#"Sample"=Table.FromRecords(Json.Document(Source,65000)[value])

in

#"Sample"

 

Regards

Helpful resources

Announcements
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!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.