March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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
Any help would be appreciated
Solved! Go to 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.
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.
Worked like a Charm 😀
@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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
13 | |
10 | |
9 | |
7 |
User | Count |
---|---|
39 | |
27 | |
16 | |
15 | |
10 |