APi with cursor based pagination

[AlexisPREVOT]

Hi,

I try to use an API who gives me different information of french entreprises.

I read the different API instructions to obtain a pagination cursor and from the site https://datachant.com/2016/06/27/cursor-based-pagination-power-query/ to get all the results.

So I wrote the following code:

let
iterations = 10,
url = "https://api.insee.fr/entreprises/sirene/V3/siren?q=periode(etatAdministratifUniteLegale%3AA)&curseur...",

FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url, [Headers=[Authorization="XXXXXXX"]])),
data = try Source[header] otherwise null,
next = try Source[header.curseurSuivant],
res = [Data=data, Next=next]
in
res,

GeneratedList =
List.Generate(
()=> [i=0, res=FnGetOnePage(url)],
each [i]<iterations and [res][Data]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data]),

in
GeneratedList

However the query only gives the first page, I cannot implement the iterations.

I think my concern is that my first API call requires "cursor = *" at the end of the API but I can't figure out how to make this work.

Can you please help me?

if that can help with the resolution, here is the response of the header provided by the API and the result returned by PowerBi:

Header of API :

Result of PowerBi :

[AlexisPREVOT]

Hi @v-easonf-msft ,

I realized that it was possible that I get the value of my next cursor by working the table differently. It would almost work.

Here is the code : 

let
Source = Json.Document(Web.Contents("https://api.insee.fr/entreprises/sirene/V3/siren?q=periode(etatAdministratifUniteLegale%3AA)&curseur...", [Headers=[Authorization="XXXXXXXXXX"]])),
#"Converti en table" = Table.FromRecords({Source}),
#"header développé" = Table.ExpandRecordColumn(#"Converti en table", "header", {"statut", "message", "total", "debut", "nombre", "curseur", "curseurSuivant"}, {"header.statut", "header.message", "header.total", "header.debut", "header.nombre", "header.curseur", "header.curseurSuivant"}),
nextpage = #"header développé"[header.curseurSuivant],
#"unitesLegales développé" = Table.ExpandListColumn(#"header développé", "unitesLegales"),
#"unitesLegales développé1" = Table.ExpandRecordColumn(#"unitesLegales développé", "unitesLegales", {"siren", "statutDiffusionUniteLegale", "dateCreationUniteLegale", "sigleUniteLegale", "sexeUniteLegale", "prenom1UniteLegale", "prenom2UniteLegale", "prenom3UniteLegale", "prenom4UniteLegale", "prenomUsuelUniteLegale", "pseudonymeUniteLegale", "identifiantAssociationUniteLegale", "trancheEffectifsUniteLegale", "anneeEffectifsUniteLegale", "dateDernierTraitementUniteLegale", "nombrePeriodesUniteLegale", "categorieEntreprise", "anneeCategorieEntreprise", "periodesUniteLegale"}, {"unitesLegales.siren", "unitesLegales.statutDiffusionUniteLegale", "unitesLegales.dateCreationUniteLegale", "unitesLegales.sigleUniteLegale", "unitesLegales.sexeUniteLegale", "unitesLegales.prenom1UniteLegale", "unitesLegales.prenom2UniteLegale", "unitesLegales.prenom3UniteLegale", "unitesLegales.prenom4UniteLegale", "unitesLegales.prenomUsuelUniteLegale", "unitesLegales.pseudonymeUniteLegale", "unitesLegales.identifiantAssociationUniteLegale", "unitesLegales.trancheEffectifsUniteLegale", "unitesLegales.anneeEffectifsUniteLegale", "unitesLegales.dateDernierTraitementUniteLegale", "unitesLegales.nombrePeriodesUniteLegale", "unitesLegales.categorieEntreprise", "unitesLegales.anneeCategorieEntreprise", "unitesLegales.periodesUniteLegale"}),
#"unitesLegales.periodesUniteLegale développé" = Table.ExpandListColumn(#"unitesLegales développé1", "unitesLegales.periodesUniteLegale"),
#"unitesLegales.periodesUniteLegale développé1" = Table.ExpandRecordColumn(#"unitesLegales.periodesUniteLegale développé", "unitesLegales.periodesUniteLegale", {"dateFin", "dateDebut", "etatAdministratifUniteLegale", "changementEtatAdministratifUniteLegale", "nomUniteLegale", "changementNomUniteLegale", "nomUsageUniteLegale", "changementNomUsageUniteLegale", "denominationUniteLegale", "changementDenominationUniteLegale", "denominationUsuelle1UniteLegale", "denominationUsuelle2UniteLegale", "denominationUsuelle3UniteLegale", "changementDenominationUsuelleUniteLegale", "categorieJuridiqueUniteLegale", "changementCategorieJuridiqueUniteLegale", "activitePrincipaleUniteLegale", "nomenclatureActivitePrincipaleUniteLegale", "changementActivitePrincipaleUniteLegale", "nicSiegeUniteLegale", "changementNicSiegeUniteLegale", "economieSocialeSolidaireUniteLegale", "changementEconomieSocialeSolidaireUniteLegale", "caractereEmployeurUniteLegale", "changementCaractereEmployeurUniteLegale"}, {"unitesLegales.periodesUniteLegale.dateFin", "unitesLegales.periodesUniteLegale.dateDebut", "unitesLegales.periodesUniteLegale.etatAdministratifUniteLegale", "unitesLegales.periodesUniteLegale.changementEtatAdministratifUniteLegale", "unitesLegales.periodesUniteLegale.nomUniteLegale", "unitesLegales.periodesUniteLegale.changementNomUniteLegale", "unitesLegales.periodesUniteLegale.nomUsageUniteLegale", "unitesLegales.periodesUniteLegale.changementNomUsageUniteLegale", "unitesLegales.periodesUniteLegale.denominationUniteLegale", "unitesLegales.periodesUniteLegale.changementDenominationUniteLegale", "unitesLegales.periodesUniteLegale.denominationUsuelle1UniteLegale", "unitesLegales.periodesUniteLegale.denominationUsuelle2UniteLegale", "unitesLegales.periodesUniteLegale.denominationUsuelle3UniteLegale", "unitesLegales.periodesUniteLegale.changementDenominationUsuelleUniteLegale", "unitesLegales.periodesUniteLegale.categorieJuridiqueUniteLegale", "unitesLegales.periodesUniteLegale.changementCategorieJuridiqueUniteLegale", "unitesLegales.periodesUniteLegale.activitePrincipaleUniteLegale", "unitesLegales.periodesUniteLegale.nomenclatureActivitePrincipaleUniteLegale", "unitesLegales.periodesUniteLegale.changementActivitePrincipaleUniteLegale", "unitesLegales.periodesUniteLegale.nicSiegeUniteLegale", "unitesLegales.periodesUniteLegale.changementNicSiegeUniteLegale", "unitesLegales.periodesUniteLegale.economieSocialeSolidaireUniteLegale", "unitesLegales.periodesUniteLegale.changementEconomieSocialeSolidaireUniteLegale", "unitesLegales.periodesUniteLegale.caractereEmployeurUniteLegale", "unitesLegales.periodesUniteLegale.changementCaractereEmployeurUniteLegale"}),
#"Type modifié" = Table.TransformColumnTypes(#"unitesLegales.periodesUniteLegale développé1",{{"header.statut", Int64.Type}, {"header.message", type text}, {"header.total", Int64.Type}, {"header.debut", Int64.Type}, {"header.nombre", Int64.Type}, {"header.curseur", type text}, {"header.curseurSuivant", type text}, {"unitesLegales.siren", Int64.Type}, {"unitesLegales.statutDiffusionUniteLegale", type text}, {"unitesLegales.dateCreationUniteLegale", type date}, {"unitesLegales.sigleUniteLegale", type any}, {"unitesLegales.sexeUniteLegale", type text}, {"unitesLegales.prenom1UniteLegale", type text}, {"unitesLegales.prenom2UniteLegale", type text}, {"unitesLegales.prenom3UniteLegale", type text}, {"unitesLegales.prenom4UniteLegale", type any}, {"unitesLegales.prenomUsuelUniteLegale", type text}, {"unitesLegales.pseudonymeUniteLegale", type any}, {"unitesLegales.identifiantAssociationUniteLegale", type any}, {"unitesLegales.trancheEffectifsUniteLegale", type text}, {"unitesLegales.anneeEffectifsUniteLegale", Int64.Type}, {"unitesLegales.dateDernierTraitementUniteLegale", type datetime}, {"unitesLegales.nombrePeriodesUniteLegale", Int64.Type}, {"unitesLegales.categorieEntreprise", type text}, {"unitesLegales.anneeCategorieEntreprise", Int64.Type}, {"unitesLegales.periodesUniteLegale.dateFin", type date}, {"unitesLegales.periodesUniteLegale.dateDebut", type date}, {"unitesLegales.periodesUniteLegale.etatAdministratifUniteLegale", type text}, {"unitesLegales.periodesUniteLegale.changementEtatAdministratifUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.nomUniteLegale", type text}, {"unitesLegales.periodesUniteLegale.changementNomUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.nomUsageUniteLegale", type any}, {"unitesLegales.periodesUniteLegale.changementNomUsageUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.denominationUniteLegale", type text}, {"unitesLegales.periodesUniteLegale.changementDenominationUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.denominationUsuelle1UniteLegale", type any}, {"unitesLegales.periodesUniteLegale.denominationUsuelle2UniteLegale", type any}, {"unitesLegales.periodesUniteLegale.denominationUsuelle3UniteLegale", type any}, {"unitesLegales.periodesUniteLegale.changementDenominationUsuelleUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.categorieJuridiqueUniteLegale", Int64.Type}, {"unitesLegales.periodesUniteLegale.changementCategorieJuridiqueUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.activitePrincipaleUniteLegale", type text}, {"unitesLegales.periodesUniteLegale.nomenclatureActivitePrincipaleUniteLegale", type text}, {"unitesLegales.periodesUniteLegale.changementActivitePrincipaleUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.nicSiegeUniteLegale", Int64.Type}, {"unitesLegales.periodesUniteLegale.changementNicSiegeUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.economieSocialeSolidaireUniteLegale", type text}, {"unitesLegales.periodesUniteLegale.changementEconomieSocialeSolidaireUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.caractereEmployeurUniteLegale", type text}, {"unitesLegales.periodesUniteLegale.changementCaractereEmployeurUniteLegale", type logical}}),

data = let
Pagination = ((List.Generate(() => #"Type modifié", //Start Value
each nextpage<>null, // condition de bouclage
each Json.Document(Web.Contents("https://api.insee.fr/entreprises/sirene/V3/siren?q=periode(etatAdministratifUniteLegale%3AA)&curseur..."& nextpage), [Headers=[Authorization="Bearer 93a322ee-09a3-371b-989e-2cd21a531b4d"]]))
))

in
Pagination,
#"Converted to Tables" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore)
in
#"Converted to Tables"

But of course I have a new error telling me that it is not possible to apply the operator & to the Text and List types here: Json.Document (Web.Contents ("https: //api.insee. en / companies / siren / V3 / siren? q = period (stateAdministrativeUniteLegale% 3AA) & cursor = "& nextpage)

How I can resolve it please ?

[v-easonf-msft]

Hi, @AlexisPREVOT 

I'm sorry, I'm not familiar with this.

If the problem is still not resolved, it is recommended to open a support ticket to let engineers look into the issue on your side.

Best Regards,
Community Support Team _ Eason

[v-easonf-msft]

Hi.  @AlexisPREVOT 

You can check if these threads could help.

Cursor-Paginated-data-from-Web-API 

Web-based-Cursor-Based-Pagination-issues 

Cursor-Paginated-data-from-Web-API-with-List-type-outcome 

How-to-make-List-Generate-work-with-cursor-based-pagination 

Best Regards,
Community Support Team _ Eason

[AlexisPREVOT]

Hi,

Thank you for your response.

Unfortunately I have already read these threads without success.

While digging yesterday, it seems to me that the problem is that my next cursor does not come from the same table as my data. When I "just" call the API, I get this:

In the header is my next cursor and in UnitesLegales is the data I want to recover

[EaglePBI]

did you eventually get a solution, I am currently in the situation now and need advice