PowerQuery Post API call to return complete data from all pages.

Hi,

Should be something like below - it assumes that we don't know the number of available pages (do we know from the response how many pages are left?) and therefore will keep incrementing "start = start + limit" until we get an error.

Hopefully your server will raise an error if we request a start that doesn't exist, otherwise it won't stop requesting.

If below doesn't work, please get back here and we'll finish it.

Please mark this as answer if it helped.

let
    apiUrl = "http://...",
    bearerToken = getbearerToken(),
    getkvAccessToken = getkvAccessToken(),
    headers = [
        #"Content-Type" = "application/json",
        #"Authorization" = "Bearer " & bearerToken,
        #"clientAppName" = "Voyager",
        #"clientFeatureName" = "Search",
        #"kvAccessToken" = getkvAccessToken,
        #"Ocp-Apim-Subscription-Key" = "ec424b08a1a14b7b97e2e9f6138767be"
    ],
    limit = 100,
    runNextRequest = (lastRequest) =>
        let
            getResponse = (startRow) =>
                let
                    requestBodyRecord = [
                        query = "((end_time ge '2022-12-31T12:01:33Z' and start_time le '2023-01-30T12:01:33Z') and search.ismatch('/.*datalog.*/', 'file_type_name', 'full', 'any'))",
                        fields = {},
                        count = true,
                        start = startRow,
                        limit = limit,
                        sort = [
                            fieldname = "end_time",
                            order = "desc"
                        ]
                    ],
                    options = [
                        Content = Json.FromValue(requestBodyRecord),
                        Headers = headers
                    ],
                    apiResponse = Json.Document(Web.Contents(apiUrl, options))
                in
                    apiResponse,
            start = if lastRequest = null then 0 else lastRequest[start] + limit,
            response = getResponse(start),
            hasError = (try response)[HasError] = true,
            currentRequest =
            // in the absence of anything in the response to tell us when to stop,
            // we will continue requesting as long as we don't get an error
            if hasError = false then [
                response = response,
                start = start
            ] else null
        in
            currentRequest,
    allPages = List.Generate(
        // initial
        () => runNextRequest(null),
        // condition
        (requestResponse) => requestResponse <> null,
        //next
        (requestResponse) => runNextRequest(requestResponse),
        //selector
        // TODO: add [docs] at the end of the line below to extract just docs
        (requestResponse) => requestResponse[response]
    )
in
    allPages