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

[Anonymous]

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

[v-jingzhang · Accepted solution]

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.

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

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

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]

Worked like a Charm 😀

[Anonymous]

@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

Can you please share how you used the DevOps APIs?

Much appreciated!

Rosie

[Anonymous]

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