Power BI Iterative API Loop

Question

I am attempting (and can successfully do so) to connect to an API and loop through several iterations of the API call in order to grab the next_page value, put it in a list and then call the list. Unfortunately, when this is published to the PBI service I am unable to refresh there and indeed 'Data Source Settings' tells me I have a 'hand-authored query'. I have attempted to follow Chris Webbs' blog post around the usage of query parameters and relative path, but if I use this I just get a constant loop of the first page that's hit.

The Start Epoch Time is a helper to ensure I only grab data less than 3 months old.

let
 iterations = 10000,          // Number of MAXIMUM iterations
url = "https://www.zopim.com/api/v2/" & "incremental/" & "chats?fields=chats(*)" & "&start_time=" & Number.ToText( StartEpochTime ),
 
 FnGetOnePage =
  (url) as record =>
    
    let
    Source1 = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer MY AUTHORIZATION KEY"]])),
    data = try Source1[chats] otherwise null, //get the data of the first page
    next = try Source1[next_page] otherwise null, // the script ask if there is another page*//*
    res = [Data=data, Next=next]
   in
    res,

    GeneratedList =
  List.Generate(
   ()=>[i=0, res = FnGetOnePage(url)],
   each [i]<iterations and [res][Data]<>null,
   each [i=[i]+1, res = FnGetOnePage([res][Next])],
   each [res][Data])

Answer 1

Lookups

If Source1 exists, but [chats] may not, you can simplify

= try Source1[chats] otherwise null

to

= Source1[chats]?

Plus it you don't lose non-lookup errors.

m-spec-operators

Chris Web Method

should be something closer to this.

let 
    Headers = [
        Accept="application/json"
    ],
    BaseUrl = "https://www.zopim.com", // very important
    Options = [
        RelativePath = "api/v2/incremental/chats",
        Headers = [
            Accept="application/json"
        ],
        Query = [
            fields = "chats(*)",
            start_time = Number.ToText( StartEpocTime )
    ],
    Response = Web.Contents(BaseUrl, Options),
    Result = Json.Document(Response) // skip if it's not JSON
in
    Result

Here's an example of a reusable Web.Contents function

helper function

let

    /*
        from: <https://github.com/ninmonkey/Ninmonkey.PowerQueryLib/blob/master/source/WebRequest_Simple.pq>
    Wrapper for Web.Contents  returns response metadata
        for options, see: <https://docs.microsoft.com/en-us/powerquery-m/web-contents#__toc360793395>
        
    Details on preventing "Refresh Errors", using 'Query' and 'RelativePath':
        - Not using Query and Relative path cause refresh errors:
            <https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/>

        - You can opt-in to Skip-Test:
            <https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/>

        - Debugging and tracing the HTTP requests
            <https://blog.crossjoin.co.uk/2019/11/17/troubleshooting-web-service-refresh-problems-in-power-bi-with-the-power-query-diagnostics-feature/>        
    update:
        - MaybeErrResponse: Quick example of parsing an error result.
        - Raw text is returned, this is useful when there's an error
        - now response[json] does not throw, when the data isn't json to begin with (false errors)

    */
    WebRequest_Simple
        =  (
            base_url as text,
            optional relative_path as nullable text,
            optional options       as nullable record
        )
        as record =>
        let

            headers = options[Headers]?, //or: ?? [ Accept = "application/json" ],

            merged_options = [
                Query = options[Query]?,
                RelativePath = relative_path,
                ManualStatusHandling = options[ManualStatusHandling]? ?? { 400, 404, 406 },
                Headers = headers
            ],

            bytes = Web.Contents(base_url, merged_options),
            response = Binary.Buffer(bytes),
            response_metadata = Value.Metadata( bytes ),
            status_code = response_metadata[Response.Status]?,
            response_text = Text.Combine( Lines.FromBinary(response,null,null, TextEncoding.Utf8), "" ),
            json = Json.Document(response),
            IsJsonX = not (try json)[HasError],
            Final = [
                request_url = metadata[Content.Uri](),
                response_text = response_text,
                status_code = status_code,
                metadata = response_metadata,
                IsJson = IsJsonX,
                response = response,

                json = if IsJsonX then json else null
            ]
        in
            Final,

    tests = {
        WebRequest_Simple("https://httpbin.org", "json"), // expect: json
        WebRequest_Simple("https://www.google.com"),       // expect: html
        WebRequest_Simple("https://httpbin.org", "/headers"),
        WebRequest_Simple("https://httpbin.org", "/status/codes/406"), // exect 404
        WebRequest_Simple("https://httpbin.org", "/status/406"), // exect 406
        WebRequest_Simple("https://httpbin.org", "/get", [ Text = "Hello World"])
    },

    FinalResults = Table.FromRecords(tests,
        type table[
            status_code = Int64.Type, request_url = text,
            metadata = record,
            response_text = text,
            IsJson = logical, json = any,
            response = binary
        ],
        MissingField.Error
    )
in
    FinalResults