Forum Discussion

Mandated's avatar
Mandated
Regular Visitor
2 years ago

Expression returns otherwise result even if valid

I'm new with the M formula language.  Currently I have a function that, when invoked, is meant to return an Employee ID column and an Employee Name column.  But if the API returns null, it causes an Expression.Error.  I tried fixing this by using try...otherwise:

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

 

The GetPeople function was already created, and requires the API key for input.  

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

 

Unfortunately, even if there is data to return, the try...otherwise construct seems to force it to evaluate to the exception's result, which is a blank table.

1 Reply