Forum Discussion
How to pass a value from one table iteratively to another API request in power bi
Hello sujeesh
you have to use a custom function for this. Create a table with your IDs, and then apply your custom function on a new column, passing the ID as parameter. I prepared for you a code example (not applying your API quering steps though). You have to put all your code here using "id" instead of your hardcoded one
let Source = #table ( {"Release_ID"}, { {"473"}, {"523"}, {"456"}, {"654"}, {"321"} } ), QueryAPI = (id)=> let //YourStepsGoHere // your reading function here("https://vsrm.dev.azure.com/fe-tfs/Advisory%20products/_apis/Release/releases/" & id &"?api-version=5.1-pre..." in id, AddColumn = Table.AddColumn ( Source, "QueryQuery", each QueryAPI([#"Release_ID"]) ) in AddColumnCopy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi,
Hope you fine. Sorry to disturb you agian
As per your sugestion I tried with the ID 571 using the code you have sent and it worked perfectly. So hope it shouldn't be an issue with the API
Source = #table
(
{"Release_ID"},
{
{"571"}, {"523"}, {"456"}, {"321"}
//{Release[id]}
//{ReleaseID}
}
)
Sample OutPut:
But when I replaced with the actual table i.e. "Release" which has a column "ID" with mutiple unique records, I get the error as below. Please correct me if i am refering the "Release" table incorrectly.
Release Table:
Query:
let
Source = Release,
Release_ID = Source[id],
/*ZIPFile1 = VSTS.Contents( VSTS.Contents("https://vsrm.dev.azure.com/fe-tfs/Advisory products/_apis/Release/releases?api-version=5.1-preview.6"),
TableData1 = Table.FromRecords(Json.Document(Source,65001)[value]),
Release_ID = TableData1[id],*/
/*Source = #table
(
{"Release_ID"},
{
{"571"}, {"523"}, {"456"}, {"321"}
//{Release[id]}
//{ReleaseID}
}
),*/
QueryAPI = (id)=>
let
Source = VSTS.Contents("https://vsrm.dev.azure.com/fe-tfs/Advisory products/_apis/Release/releases/"& id &"?api-version=5.1-preview.6")
//ZIPFile = Table.FromRecords(Json.Document(Source,65001)[value])
in
Source,
AddColumn = Table.AddColumn
(
Source,
"QueryQuery",
each QueryAPI([#"Release_ID"])
),
#"Parsed JSON" = Table.TransformColumns(AddColumn,{{"QueryQuery", Json.Document}}),
#"Expanded QueryQuery" = Table.ExpandRecordColumn(#"Parsed JSON", "QueryQuery", {"id", "name", "status", "createdOn", "modifiedOn", "modifiedBy", "createdBy", "createdFor", "environments", "variables", "variableGroups", "artifacts", "releaseDefinition", "releaseDefinitionRevision", "description", "reason", "releaseNameFormat", "keepForever", "definitionSnapshotRevision", "logsContainerUrl", "url", "_links", "tags", "triggeringArtifactAlias", "projectReference", "properties"}, {"QueryQuery.id", "QueryQuery.name", "QueryQuery.status", "QueryQuery.createdOn", "QueryQuery.modifiedOn", "QueryQuery.modifiedBy", "QueryQuery.createdBy", "QueryQuery.createdFor", "QueryQuery.environments", "QueryQuery.variables", "QueryQuery.variableGroups", "QueryQuery.artifacts", "QueryQuery.releaseDefinition", "QueryQuery.releaseDefinitionRevision", "QueryQuery.description", "QueryQuery.reason", "QueryQuery.releaseNameFormat", "QueryQuery.keepForever", "QueryQuery.definitionSnapshotRevision", "QueryQuery.logsContainerUrl", "QueryQuery.url", "QueryQuery._links", "QueryQuery.tags", "QueryQuery.triggeringArtifactAlias", "QueryQuery.projectReference", "QueryQuery.properties"})
in
#"Expanded QueryQuery"
Can you please suggest me what should i do to make this working
Reference API:
API_1 : https://vsrm.dev.azure.com/fe-tfs/Advisory%20products/_apis/Release/releases?$top=80&api-version=5.1-preview.6 ==>This table has a list of ID's which needs to be passed to the second API.
Thanks & Regards,
Sujeesh
Hello sujeesh
the table I provided containt the ID-Column formated as text, whereas in your example it shows a number. Try to change this.
Jimmy
Thank you Jimmy801. I feel i am almost there to get it worked through your support.
It perfectly worked when the table has one value
For example:
Table Name: Table_Ex
Table with one row data
Output:
Output for table with one table
But when my table has mutiple row data. It shows 1 key specified for multiple value
For example:
Table Name: Table_Ex
Table with multiple rows
Output:
I tried to store the data as list to see if it helps. But no luck, it shows error as below.Hope I am making error in the way i am refering the table data(Table_EX) iteratively to "AddColumn" when there is mutiple row data
Query used:
let
Source = Table_EX[id],
#"Release_ID" = Source[id],/* Source = #table
(
{"Release_ID"},
{
//{"473"}, {"523"}, {"456"}, {"321"}
Table_EX[id]
}
),*/
QueryAPI = (id)=>
let
//YourStepsGoHere
// your reading function here("https://vsrm.dev.azure.com/fe-tfs/Advisory%20products/_apis/Release/releases/" & id &"?api-version=5.1-pre..."
Source = VSTS.Contents("https://vsrm.dev.azure.com/fe-tfs/Advisory products/_apis/Release/releases/" & id &"?api-version=5.1-preview.6")
//ZIPFile = Table.FromRecords(Json.Document(Source,65001)[value])
in
Source,AddColumn = Table.AddColumn
(
Source,
"QueryQuery",
each QueryAPI([#"Release_ID"])
)
in
AddColumnThanks & Regards,
Sujeesh