How to pass a value from one table iteratively to another API request in power bi

[sujeesh]

Hi,

I am a newbie to power bi. I have a requirement to pass a value from one table iteratively to another API request query string. Output all the iteration results in a table along with a new column in the API output as "Count" for which the data needs to be updated based on the rule(if an environment name column in the API has a value "Live" then 1 else 0.

Can you please tell me how to perform this requirement

For example

Table1:

Release_ID

473

523

456

654

321

API:

https://vsrm.dev.azure.com/fe-tfs/Advisory%20products/_apis/Release/releases/456?api-version=5.1-pre...

Thanks & Regards,

Sujeesh

[Jimmy801 · Accepted solution]

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
	AddColumn

Copy 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

[sujeesh]

Hi @Jimmy801 

Thank you so much for looking into my clarification.

As per your suggestion, I have tried the below changes and it worked perfectly. but when tried to replace the table with my actual table i.e. Release, it is showing the error as 

My release table has a column name as the "id", which is one I am trying to input into the query string.

let
Source = Release,
Release_ID = Source[id],

// Source = #table
//(
// {"Release_ID"},
// {
// {"473"}, {"523"}, {"456"}, {"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..."
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
AddColumn

Also, I got the file as the JSON file. I tried to display it in table format using the below command. but it is not working. 

  = Table.FromRecords(Json.Document(Source,65001)[value])

Thanks & Regards,

Sujeesh

[Jimmy801]

Hello @sujeesh 

i suppose that this error message is not coming from your table, but from the API itself, because it was passed a wrong ID. Is that feasable? Please check that

about your second question. You will have a two column table with Release ID and the output from the API (don't know the API so I don't know the exact format) but i would the code add to the custom function, in order you have the final table in your QueryQuery-column. Means that would have to replace this code:

//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,

with this

//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
ZIPFile ,

But is rather guessing. Give it at try 🙂

Bye

Jimmy

[sujeesh]

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... ==>This table has a list of ID's which needs to be passed to the second API.

API_2 : https://vsrm.dev.azure.com/fe-tfs/Advisory%20products/_apis/Release/releases/571?api-version=5.1-pre...

Thanks & Regards,

Sujeesh

[Jimmy801]

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

[sujeesh]

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
AddColumn

Thanks & Regards,

Sujeesh

[Jimmy801]

Hello

When you are invoking the function, you are passing a column called Release_Id but your new table doesn't contain this column.. its called only id

Jimmy

[sujeesh]

@Jimmy801 Thank You. It's working perfectly. I have marked this has a solution

[sujeesh]

Hi @Jimmy801 

Hope you fine. Sorry to disturb you again

As per syour code when i try with the ID: 571 I get the perfect answer

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. I am not sure if i am refering the Release table incorrectly. Please correct me if I am wrong

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"

API Used

API_1 : https://vsrm.dev.azure.com/fe-tfs/Advisory%20products/_apis/Release/releases?$top=80&api-version=5.1... ==>This table has a list of ID's which needs to be passed to the second API.

API_2 : https://vsrm.dev.azure.com/fe-tfs/Advisory%20products/_apis/Release/releases/571?api-version=5.1-pre...

Can you please suggest me what should i do to make this working

Thanks & Regards,

Sujeesh