Check if JSON Attribute Exists

[ChadC]

Hi, 

I'm working with a paginated API. The initial fetch will return a JSON payload like:

{
  "data": [
   ...
  ],
  "links": {
    "next": "https://api.outreach.io/api/v2/prospects?page[limit]=50&page[offset]=200"
  }
}

My issue arises on the final fetch, where no "links" attribute exists. How can I check if this attribute exists and handle the scenario in which it inevitably does not?

I've tried writing the following in M with no success. I get the message: "Details: "The field 'next' of the record wasn't found.""

GetPage = (Index, Scope) =>
    let
        prevIndex = Index - 1,
        limit = Number.ToText(LimitPerPage),
        offset = Number.ToText(prevIndex * LimitPerPage),
        JSON = GetJSON(api_source_uri & Scope & "?page[limit]=" & limit & "&page[offset]=" & offset),
        Value = JSON,
        JSON_Links = if JSON[links] <> null then GetJSON(JSON[links][next]) else null,
        Output = if JSON_Links <> null then JSON_Links[data] else []
    in
        Output;

Any help is appreciated. 

Thanks!

[Stachu · Accepted solution]

I'd try to use

try ... otherwise ...

syntax to catch errors, see more here
https://blog.gbrueckl.at/2013/12/error-handling-in-power-query/

[ChadC]

Thanks @Stachu , that was what I was looking for!

Updated function:

GetPage = (Index as number, Scope as text, Offset as number) =>
    let
        // Calculate the offset. 
        _offset = Number.ToText(Index * LimitPerPage + Offset),
        // Try to fetch a JSON payload. If an error occurs, return a JSON document with an empty "data" attribute.
        JSON = try GetJSON(api_source_uri & Scope & "?page[limit]=" & strLimitPerPage & "&page[offset]=" & _offset) otherwise Json.Document("{""data"": []}"),
        // Get the ""data"" attribute of the JSON document.
        Page = JSON[data]
    in
        Page;

[v-xicai]

Hi @ChadC ,

If you still have this issue for Power BI, you'd better create a support ticket in Power BI Support to get further help.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.