cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sujeesh
Helper I
Helper I

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

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

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

image.png

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

View solution in original post

9 REPLIES 9
Jimmy801
Community Champion
Community Champion

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

image.png

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

View solution in original post

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 

 

Snap 1.png

 

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
Community Champion
Community Champion

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

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:

Snap2.png

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:

Snap3.png

 

Snap1.png

 

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
Community Champion
Community Champion

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 dataTable with one row data

 

Output:

Output for table with one tableOutput 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 rowsTable with multiple rows

 

Output:

Snap7.png

 

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

 

Snap9.png

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
Community Champion
Community Champion

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

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

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:

Snap2.png

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:

Snap3.png

 

Snap1.png

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors