How to iteratively pass a header parameter in to a API url to get the complete list from the API.

[sujeesh]

Hi,

I have a below requirement to iteratively pass a header parameter in to a API url to get the complete list from the API.

1.Load the below API URL

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

The response will load the first 50 sets of data with unique release id in Json format. Also an response header with the name “x-ms-continuationtoken”= 538

2.To load the remaining set of list from the API, I need to pass the parameter “x-ms-continuationtoken  =538” to the URL iteratively to get all the list from the API.

 I am new to power bi and just started using this tool.. Can someone please help me to get around this requirement.

Thanks & Regards,

Sujeesh

[artemus · Accepted solution]

It will look something like:

let
    NextResult = (token) =>
        let
            URL = "https://vsrm.dev.azure.com/fe-tfs/Advisory%20products/_apis/release/releases?api-version=5.1",
            URLWithContinuation = 
                if token = null then
                    URL
                else
                    URL & "&continuationToken=" & token, 
            Content = VSTS.Contents(URLWithContinuation),
            AsJson = Json.Document(Content)[value],
            continuationToken = Value.Metadata(Content)[Headers][#"x-ms-continuationtoken"]?,
            Result = 
                if continuationToken = null then
                    AsJson
                else
                    AsJson & @NextResult(continuationToken)
        in
            Result,
    AsTable = Table.FromRecords(NextResult(null))
in
    AsTable

[Srikanth7Gunnam]

Hello, I am currently working on a similar project where I need to retrieve complete test points data. I have written a function with testplanid and test suited as parameters.

let

  PointsAPICall = (TestPlanId as text,TestSuiteId as text) =>

    let

    Source =

    try

    Json.Document(Web.Contents("https://dev.azure.com/{Org}/{project}/_apis/testplan/Plans",[RelativePath= TestPlanId &"/Suites/" & TestSuiteId & "/TestPoint?api-version=6.0-preview.2"]))

    otherwise

    null

in

    Source

in

  PointsAPICall

But, I am only getting the first batch of test points.  Could you please help me pass "continuationToken" to the above code?

When I pass the continuationToken parameter, nothing is being retrieved.

Thanks in advance,

Sri

[iSaurabh]

Hi,

getting parameter error at

AsTable = Table.FromRecords(NextResult(null))

when the token is null...any pointers??

Thanks in advance

[sujeesh]

Thank you @artemus . The solution worked perfectly.