Forum Discussion

sujeesh's avatar
sujeesh
Helper I
6 years ago
Solved

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 colum...
Tutorial Requests
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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's avatar
sujeesh
Helper I
6 years ago

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's avatar
Jimmy801
Community Champion
6 years ago
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's avatar
    sujeesh
    Helper I
    6 years ago

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