Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Thanks & Regards,
Sujeesh
Solved! Go to 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
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
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 = #table AddColumn = Table.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
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:
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.
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
Output:
But when my table has mutiple row data. It shows 1 key specified for multiple value
For example:
Table Name: Table_Ex
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
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.
Can you please suggest me what should i do to make this working
Thanks & Regards,
Sujeesh
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.