Power Query & VBA Issue

[gravanoc]

Part of my script is responsible for creating a query. Technically I could create the query ahead of time and just reference it in the script, but I'd like to better understand the issue I'm having. PQ is returning an error (see pic #1), which in turn causes VBA to throw an error since it cannot proceed to the next query. The PQ formula has two parts: 1) a function called GetPeople is using a GraphQL query to instruct the API on what it needs, and sets up the method by which it can return this information, 2) the function is invoked with an API key, returning a table of employee IDs and names. VBA supplements this process by automating the function invocation.

The frustrating part is that I seem to be using the correct syntax to handle the null value error, but the results are not correct. I've attached some scripts below.

PQ Function script:

let
  Source = (apiToken as text, optional endCursor as text, optional data as list) =>
    let
      endCursor = if endCursor is null then "" else endCursor,
      query = "{
        ""query"": ""
    {
      results: PeopleOnSiteHistory(First: 2, After: \""" & endCursor &"\"") {
        PageInfo {
          HasNextPage
          EndCursor
        }
        Items {
          PersonId
          PersonName
        }
      }
    }
    ""
    }",

      JSON = Web.Contents("https://cloud.3dsafety.com.au/graphql",
          [
              Headers = [#"X-API-Key"=apiToken, #"Content-Type"="application/json"],
              Content = Text.ToBinary(query)
          ]
      ),
      Source = Json.Document(JSON),
      pageInfo = Source[data][results][PageInfo],
      items = Source[data][results][Items],
      appendedData =
        if pageInfo[HasNextPage] = true and data is null then
          List.Combine({{}, items})
        else List.Combine({data, items}),
          output =
            if pageInfo[HasNextPage] = true then
              @GetPeople(apiToken, pageInfo[EndCursor], appendedData)
            else
              Table.FromList(appendedData, Record.FieldValues, {"PersonId", "PersonName"})
    in
      output
in
  Source

The Invoked Function (with API key scrubbed):

let
    Source = GetPeople("API-xxxxx", null, null),
    ErrorHandler = try [Source] otherwise #table({"Column1", "Column2"}, {})
in
    ErrorHandler

The VBA code isn't the source of the error, so I won't paste that here. The error-handling takes place in the invoked function using try...otherwise syntax. The problem is that it is executing the otherwise portion even when there are legitimate values to return, thus defeating the purpose.

 

[GraH]

It's a standard Power Query Error. Since I can't tell at which step the error occurs, you can try to add in your function argument "as nullable text", "as nullable list".