Incomplete json schema records from API calls

[CoEAdmin]

I appear to be missing data in results returned from a REST API call. I first call a "GetProviders" method to retrieve all the companies I'm interested in, then for each company I call another method (GetProviderById) to get extra details about it. The expected return data can contain dozens of fields and records in json format but none are mandatory.

What I'm seeing is that some companies I know have a full dataset (validated by calling it outside of the main process) has much of it missing in the main processing loop. 

To me it looks like the first call to GetProviderById doesn't have a full dataset returned and as such "fixes" the schema meaning subsequent calls that do have more data are trimmed to fit the first call's schema. Is that a correct assumption? If not, how does this work and is there a way to ensure I always get the full dataset returned where it's available and nulls when it's not.

The code below is how I'm calling the GetProviderById for each of the Providers in the "ListofProviders" table.

 AddApiResponse = Table.AddColumn(

         #"ListofProviders",

        "ApiResponse",

        each FnGetProviderById(Text.From([providerId])))

Here's the function:

(id) =>

let

  Source = Json.Document(Web.Contents("https://api.service.cqc.org.uk/public/v1/providers/" & id, [

        Headers = [#"accept"="*/*", #"Content-Type"="application/json-patch+json", #"Ocp-Apim-Subscription-Key"=APIKey]       

    ]))

in

  Source

Any help appreciated.

[Anonymous]

Hi @CoEAdmin ,

// To me it looks like the first call to GetProviderById doesn't have a full dataset returned and as such "fixes" the schema meaning subsequent calls that do have more data are trimmed to fit the first call's schema.

Custom columns should be calculated once per row iteration and custom columns may return a different schema. As a validation, you can pass the providerId separately to see the results.

Also try this to initialize a schema with all potential fields pre-included:

//FnGetProviderById
(id) =>
  let
    // Add all potential fields you expect
    Template = [a = null, b = null, c = null, d = null, e = null], 
    Source = Json.Document(
      Web.Contents(
        "https://api.service.cqc.org.uk/public/v1/providers/" & id, 
        [
          Headers = [
            #"accept"                    = "*/*", 
            #"Content-Type"              = "application/json-patch+json", 
            #"Ocp-Apim-Subscription-Key" = APIKey
          ]
        ]
      )
    ), 
    MergedRecord = Record.Combine({Template, Source})
  in
    MergedRecord

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group