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 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-preview.6

 

Thanks & Regards,

Sujeesh

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

9 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    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

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

        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