Forum Discussion

CoEAdmin's avatar
CoEAdmin
Regular Visitor
2 years ago

Incomplete json schema records from API calls

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.
Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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