Connecting API with pagination

[esllen]

Hello,

I've been working with Power BI for a short time and I'm having trouble importing data from a paginated Json.
Each page has 25 records. This pagination needs to be dynamic because with time the number of these pages will get bigger.

let
    Fonte = Json.Document(Web.Contents("https://api.clinicarx.com.br/v1/patients", [Headers=[Accept="application/json"]])),
    #"Convertido para Tabela" = Table.FromRecords({Fonte})
in
    Fonte

For the following pages, the URL is as follows: 

"https://api.clinicarx.com.br/v1/patients?page=2"

The documentation is open and simplified, but i have difficulty for not habing in-depth knowledge of programming. I leave here the link : https://clinicarx.com.br/developer

I need a big help to get power bi to load the other pages in a single table. And how will I do it?

[esllen]

Hello, I'm on the way, but I have a problem. 

I created a function called "Dados" with the data fo import.

Dados: 

(offset) =>  

let
    Fonte = Json.Document(Web.Contents("https://api.clinicarx.com.br/v1/patients?page=" & Number.ToText(offset), [Headers=[Accept="application/json"]])),

    #"Convertido para Tabela" = Table.FromRecords({Fonte}),
    #"data Expandido" = Table.ExpandListColumn(#"Convertido para Tabela", "data"),
    #"data Expandido1" = Table.ExpandRecordColumn(#"data Expandido", "data", {"id", "document", "name", "email", "birthday", "sex", "phone1", "phone2", "tenancy_id", "health_plan_id", "health_plan_name", "health_plan_document", "has_national_health", "has_particular", "applied_vaccines", "treatments", "clinical_conditions", "uuid", "recommended_vaccines", "phones"}, {"data.id", "data.document", "data.name", "data.email", "data.birthday", "data.sex", "data.phone1", "data.phone2", "data.tenancy_id", "data.health_plan_id", "data.health_plan_name", "data.health_plan_document", "data.has_national_health", "data.has_particular", "data.applied_vaccines", "data.treatments", "data.clinical_conditions", "data.uuid", "data.recommended_vaccines", "data.phones"}),
    #"pagination Expandido" = Table.ExpandRecordColumn(#"data Expandido1", "pagination", {"page_count", "current_page", "has_next_page", "has_prev_page", "count", "limit"}, {"pagination.page_count", "pagination.current_page", "pagination.has_next_page", "pagination.has_prev_page", "pagination.count", "pagination.limit"}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"pagination Expandido",{{"success", type logical}, {"data.id", Int64.Type}, {"data.document", Int64.Type}, {"data.name", type text}, {"data.email", type text}, {"data.birthday", type date}, {"data.sex", type text}, {"data.phone1", type text}, {"data.phone2", type text}, {"data.tenancy_id", type text}, {"data.health_plan_id", type any}, {"data.health_plan_name", type any}, {"data.health_plan_document", type any}, {"data.has_national_health", type logical}, {"data.has_particular", type logical}, {"data.applied_vaccines", type any}, {"data.treatments", type any}, {"data.clinical_conditions", type any}, {"data.uuid", type text}, {"data.recommended_vaccines", type any}, {"data.phones", type any}, {"pagination.page_count", Int64.Type}, {"pagination.current_page", Int64.Type}, {"pagination.has_next_page", type logical}, {"pagination.has_prev_page", type logical}, {"pagination.count", Int64.Type}, {"pagination.limit", type any}})

in
    #"Tipo Alterado"

The "consulta1" with the List.Generate to bring in the data.

Consulta1:

let
    Fonte = List.Generate(
    () => [offset = 0, lista = dados(0)],
    each [offset] <= 1000,
    each [offset = [offset] + 1, lista = dados([offset])],
    each[lista]
),
    #"Convertido para Tabela" = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 Expandido" = Table.ExpandTableColumn(#"Convertido para Tabela", "Column1", {"success", "data.id", "data.document", "data.name", "data.email", "data.birthday", "data.sex", "data.phone1", "data.phone2", "data.tenancy_id", "data.health_plan_id", "data.health_plan_name", "data.health_plan_document", "data.has_national_health", "data.has_particular", "data.applied_vaccines", "data.treatments", "data.clinical_conditions", "data.uuid", "data.recommended_vaccines", "data.phones", "pagination.page_count", "pagination.current_page", "pagination.has_next_page", "pagination.has_prev_page", "pagination.count", "pagination.limit"}, {"success", "data.id", "data.document", "data.name", "data.email", "data.birthday", "data.sex", "data.phone1", "data.phone2", "data.tenancy_id", "data.health_plan_id", "data.health_plan_name", "data.health_plan_document", "data.has_national_health", "data.has_particular", "data.applied_vaccines", "data.treatments", "data.clinical_conditions", "data.uuid", "data.recommended_vaccines", "data.phones", "pagination.page_count", "pagination.current_page", "pagination.has_next_page", "pagination.has_prev_page", "pagination.count", "pagination.limit"})

in
    #"Column1 Expandido"

 The problem is that it has data up to pagination 287. When PowerBI tries to fetch page 288, an error occurs when loaded. 

Loading:

This pagination will increase over time, so I need to be dynamic to load to the pagination that contains the data.

each [offset] <= 1000,

[amitchandak]

@esllen , refer if this approach can help

https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78